For this project I decided to put together a dynamic timesheet that prevents mistakes and a payroll page for each employee with a one quarter payroll page.

Features

Timesheet Features

I created an automated timesheet because I am forced to check if the amount of time employees match the number of hours worked. Miscounting happens frequently.

Data Validation

Data validation can be used to allow rapid input of data when the possible choices are limited, to prevent invalid choices, and prevent errors.

In the version of this I created for work, it was helpful because there are a small number of employees. The employee's name can be selected from a data validation dropdown.

I created a list of the first day of every biweekly payroll period. I used this to create a dropdown validation for the first date on every payroll sheet. I also created a data validation list of times to create a dropdown box for each end time and each start time.

Conditional Formatting

I used conditional highlighting to show whenever a logically impossible time period is listed. An end time must be after the start time. I used the condition that the end time is greater than the start time.

In this case it is not possible for an employee to work from 9:00 A.M to 5:00 A.M in the same day.

Time and Date Data Formatting

Date and time formatting can enable additional data manipulation over simple text. I used date and time formatting for the times to enable mathematical functions using them.

I can added the differences between the end and start times to measure the number of hours worked each day. I used formulas in the date column to automatically input all of the rest of the dates once there first date is entered. Since excel treats dates as a number of days since 1970, adding one to a date time returns the same time on the next day. Each subsequent cell adds one day by adding one.

rI formatted the hours column using a custom h:mm format so I could add and subtract the number of hours based on the start and end times.

Automated Payroll Features

I created an automated payroll worksheet because I have to manually look up values to input using state and federal tax tables.

XLOOKUPS

The main feature is use of XLOOKUP formulas. In the work draft of this tool there never would have been a need to calculate Federal payroll taxes for a biweekly gross income above $2,200. Income below this level has a set dollar amount based on the range. So I was able to use a simple XLOOKUP formula.

=XLOOKUP($D28,Federal[>=],Federal[Tax],"-",-1 )

I created a table named Federal. The column named ">=" contains the value that the gross income that the first references is compared to and the Tax column is the value looked up. If nothing is found "-" is returned. -1 Indicates that if the number in argument 1 does not find an exact match the closest lower number is used for the second argument to find the value to return from the third argument.

I had to use a complex XLOOKUP formula for the state withholding taxes.

This formula has two key differences from the federal formula. First, the ranges for each tax value are greater or equal to the the low number and less than the largest number. The Ohio state tables require income be greater than the low threshold and less than or equal to the high threshold. This fact combined with using -1 in argument 4 creates an issue. If the gross income is exactly equal to the minimum threshold then that row will be used. I need the next row to be chosen. I added .001 to the second argument so that whatever the income is, the value used will be larger than the minimum threshold. I avoided moving to a higher threshold by adding a number that is smaller than one cent since it is the lowest denomination of currency.

The other difference between the state formula and the federal one is due to the tax rules. Once gross income exceeds $1,538, the tax is determined as a base number plus the percentage over the minimum threshold (ie 34.68 + 3.5% of the excess over $1,538).

To deal with three thresholds like this I used IFS to create conditions to apply if the gross income is larger than the thresholds with set values.