A timesheet looks simple until the first overnight shift, the first unpaid lunch or the first week that crosses 40 hours. Then the totals stop matching the clock and the payroll file is wrong by a few dollars that are hard to trace.
These are the formulas that make a weekly timesheet behave — plus the formatting rule that explains why 12:00 sometimes shows as 0.5.
The basic hours-worked formula
Enter clock-in in B2 and clock-out in C2, then calculate:
=(C2-B2)*24
Excel stores a day as the number 1, so 08:00 is 0.333… and 16:00 is 0.667…. Subtracting gives the fraction of a day worked; multiplying by 24 converts it to decimal hours.
Format the time cells as Time and the result cell as Number with two decimals. If the result shows a time such as 8:00, the cell inherited a time format — change it to General or Number.
Overnight shifts: use MOD
If someone clocks in at 22:00 and out at 06:00, the subtraction is negative and Excel displays ######### or a negative time error.
=MOD(C2-B2,1)*24
MOD wraps the negative difference back into a 24-hour clock, returning 8 hours. Apply this formula to every row if overnight shifts are possible; it behaves identically for daytime shifts.
Subtracting unpaid breaks
Store the break in minutes in D2 and subtract it in the same units:
=(C2-B2)*24-D2/60 or, with overnight support, =MOD(C2-B2,1)*24-D2/60.
If the break is stored as an Excel time (0:30), subtract D2*24 from decimal hours or D2 from the raw time difference. Mixing minutes and time values is the most common cause of a 30-minute break being deducted as 30 hours.
Wrap the result in MAX(0, …) if a long break could make the shift negative: =MAX(0,MOD(C2-B2,1)*24-D2/60).
Weekly totals, regular time and overtime
Put the daily hours in E2:E8 and total them:
=SUM(E2:E8)
For a 40-hour weekly threshold:
- Regular hours:
=MIN(SUM(E2:E8),40) - Overtime hours:
=MAX(0,SUM(E2:E8)-40) - Gross pay:
=regular*rate+overtime*rate*1.5
Keep the rate and multiplier in their own cells rather than typing them into the formula. When the rate changes, one edit updates the whole sheet — and a marker can see where the assumption came from.
Totals above 24 hours
A weekly total may exceed 24 hours. A custom format of h:mm wraps at 24 and shows 08:00 for 32 hours. Use square brackets — [h]:mm — to display the true total.
For pay calculations, decimal hours are usually easier: =SUM(E2:E8) with a Number format, not a Time format.
A five-minute audit before you submit
- Check every time cell is a real time, not text. Left-aligned times are usually text.
- Confirm the result cell is Number, not Time, if you are multiplying by an hourly rate.
- Test one overnight row and one row with a long break.
- Change the rate cell and confirm the pay total recalculates.
- Verify the overtime threshold matches the rule in the brief — weekly, daily or averaged.
When the timesheet is part of a graded model
Markers look for traceable assumptions: rate, threshold and multiplier in separate cells, a clear regular-versus-overtime split, and no hardcoded totals. A tidy timesheet with documented inputs scores better than a shorter one with numbers typed into formulas.
Use the free Excel timesheet calculator to check your week and copy the formulas, or browse the function reference for MOD, SUM and MIN. Need the workbook built and explained before the deadline? Send it to an Excel expert for a fixed quote.
