Employee Timesheet Calculator Excel Template

Enter the seven days from Monday to Sunday, then type the Time In, Lunch Out, Lunch In, and Time Out for each day. Mark a day off with a dash so the row stays readable.

Select all four time columns and press Ctrl+1 to open Format Cells. On the Number tab, pick Time and choose a style such as 1:30 PM, or the 24-hour style if your team prefers it. This matters because Excel stores a time as a fraction of a day, and the display format is only a label on top of that number.

Gross hours are the time between clock-in and clock-out. Subtract Time In from Time Out, then wrap that difference in MOD with a divisor of 1. If the shift crosses midnight, the raw difference is negative, and MOD turns it back into the correct fraction of a day.

Excel counts a day as 1, so multiply by 24 to get hours, then round to two decimals. As you type, the AutoComplete list shows MOD, MODE and similar names, so press Tab on the right one instead of typing the whole name. Marcus's Monday comes out at 9.50 hours.

The break column uses the same logic, applied to Lunch Out and Lunch In. Then net hours are simply gross minus break, rounded to two decimals. Monday becomes 9.50 minus 1.00, which gives 8.50.

Click the net cell and press F2. Excel outlines every cell the formula uses in a different color, so you can check at a glance that it points at the gross and break cells of the same row. Copy the formulas down through Saturday. The Sunday row holds zeros because Marcus did not work, so add formulas there if someone does.

First, total the net hours for the week. Marcus has 47.50. Regular hours are the smaller of 40 and that total, which is where MIN helps. Overtime is the larger of zero and the total minus 40, which is where MAX helps. The zero floor stops overtime from going negative in a short week.

Use Insert Function to open the Function Arguments box for each one. It shows what every argument evaluates to before you confirm, which makes mistakes easy to spot. The table under the log reaches the same answer with an IF test, so the two should always match.

Gross pay has two parts. Regular hours get multiplied by the hourly rate, and overtime hours get multiplied by the rate and then by 1.5. Add the two parts together. For Marcus, 40 hours at $32.50 is $1,300.00 and 7.50 overtime hours add $365.63, so the total is $1,665.63.

Open Format Cells again, choose Accounting, and keep two decimals. Be aware that Excel shows rounded cents but stores the full value, here 1,665.625. If your payroll needs exact cents, wrap the pay calculation in ROUND so the stored value matches what you see.