Build a Timesheet in Excel: Overnight Shifts, Breaks, and Overtime That Actually Works
Time arithmetic in Excel breaks in three predictable places: shifts crossing midnight, decimal hours, and weekly overtime thresholds. Here is a timesheet that handles all three.
Build a Timesheet in Excel
Excel stores a time as a fraction of a day. Nine in the morning is 0.375, because it is three eighths of the way through. Everything that goes wrong with timesheets follows from that one fact, and everything that goes right follows from working with it rather than against it.
Try the timesheet calculatorTrack weekly hours with custom daily and weekly overtime thresholds and export the result.Step 1: hours worked in a day
Columns: B for clock in, C for clock out, D for unpaid break in minutes, E for hours worked. Format B and C as time, and E as a plain number with two decimals.
Step 2: shifts that cross midnight
A shift from 22:00 to 06:00 gives a negative result with the simple formula, because 06:00 (0.25) minus 22:00 (0.9167) is negative. Excel displays this as a row of hash symbols.
The only case MOD cannot handle is a shift longer than 24 hours, which it would wrap incorrectly. If that is possible in your operation, store full date-and-time values rather than times alone and subtract those instead.
Step 3: weekly overtime
Under the US Fair Labor Standards Act, non-exempt hours beyond 40 in a workweek are paid at 1.5 times the regular rate. Overtime is assessed per workweek and cannot be averaged across two weeks, which is why a 30-hour week followed by a 50-hour week still owes ten hours of premium.
Step 4: states with a daily threshold
Several US states, California among them, apply a daily threshold as well: over 8 hours in a day at 1.5x, over 12 at double time. Where both a daily and a weekly rule apply, the employee gets whichever is more favourable, and the hours already paid at a premium daily are not counted again weekly.
Swipe sideways to compare columns.
| Column | Formula | Meaning |
|---|---|---|
| F (Regular) | =MIN(E2, 8) | First 8 hours |
| G (Time and a half) | =MIN(MAX(0, E2-8), 4) | Hours 9 to 12 |
| H (Double time) | =MAX(0, E2-12) | Beyond 12 |
Sum each column for the week. Then apply the weekly rule only to regular hours above 40, so a day already paid at a premium is not double counted. If your workforce spans several states, drive the thresholds from a lookup table keyed on state rather than hard-coding 8 and 40, because the numbers differ and change.
Step 5: rounding to the nearest interval
Many employers round clock times to the nearest six or fifteen minutes. Under US rules the rounding must be neutral over time, meaning it cannot systematically favour the employer.
Step 6: showing hours in both formats
Payroll wants decimal hours, but people reading the sheet want to see 8:05. Show both, with the decimal as the value payroll uses.
Step 7: guard against bad entries
- Blank rows: wrap the hours formula in =IF(OR(B2="", C2=""), "", ...) so empty days show blank rather than zero or an error.
- Break longer than the shift: =IF(D2/60 > (MOD(C2-B2,1))*24, "Check break", ...) flags an impossible entry.
- Implausible shifts: conditional formatting on any day over 16 hours catches a mistyped AM or PM.
- Locked formula columns: protect the calculation columns so staff enter times only.
All of this works identically in Google Sheets. MOD, MIN, MAX, MROUND, and TEXT take the same arguments, and time is stored as the same fraction of a day.
Try the overtime and shift differential calculatorHandle daily and weekly overtime, double time, and night or weekend differentials together.How do I calculate hours worked in Excel across midnight?
Use =(MOD(C2-B2, 1))*24 rather than =(C2-B2)*24. MOD wraps the negative difference back into a valid range, so a 22:00 to 06:00 shift returns 8 hours instead of an error.
How do I convert time to decimal hours in Excel?
Multiply the time difference by 24 and format the cell as a number. 8 hours 30 minutes becomes 8.5, which is what payroll multiplies by the rate. Leaving it formatted as time gives 8:30, which cannot be multiplied correctly.
What is the Excel formula for overtime over 40 hours?
Regular hours are =MIN(total, 40) and overtime is =MAX(0, total-40). Pay is regular × rate + overtime × rate × 1.5. Assess it per workweek; overtime cannot be averaged across two weeks.
Why does my weekly total show 22:00 instead of 46 hours?
The cell format resets at 24 hours. Use the custom format [h]:mm, with the square brackets, so totals beyond a day display correctly.
Written by
Do The Calculation Team
Do The Calculation
Do The Calculation is built by a small team of data analysts and spreadsheet developers. Where a guide depends on a published formula, standard, or government rule, the calculator it links to names that source directly so you can check the number yourself.
About the team