The classic timesheet formula breaks on the shift that ends after midnight. Subtract a 10:00 PM start from a 6:00 AM finish and the result is negative.
This tutorial builds a weekly timesheet in Google Sheets that handles that shift, takes off unpaid breaks, splits regular hours from overtime and shows totals past 24 hours. The layout is simplified, and the example is a made-up week with 49.5 hours.
1. One row per day
Put these headers in row 1:
| A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|
| Date | Day | Project | Time in | Time out | Break (min) | Hours worked |
Type the Monday of the week in A2, then in A3:
=A2+1
Copy A3 down to A8. In B2, copy down to B8:
=TEXT(A2, "ddd")
Type times the way you normally would, such as 8:30 AM or 5:00 PM, and format D:E with Format > Number > Time. For Project, select C2:C8, open Data > Data validation > Add rule and choose Dropdown (from a range) with your project list.
2. Hours worked, including overnight
In G2:
=IF(OR(D2="", E2=""), 0, MAX(0, MOD(E2-D2, 1)*24 - N(F2)/60))
Times are stored as fractions of a day. E2-D2 is the span, multiplying by 24 turns it into hours, and N(F2)/60 removes the break. The MOD is the part that handles midnight. For 10:00 PM to 6:00 AM, E2-D2 comes out as a negative fraction, and MOD(..., 1) wraps it to a positive one. Without it, the same cells give -16 hours. MAX(0, ...) stops a long break from producing a negative day.
An in time and an out time that are identical return 0, not 24 hours.
Copy G2 down to G8. The sample week:
| Day | In | Out | Break | Hours |
|---|---|---|---|---|
| Mon | 8:30 AM | 5:30 PM | 30 | 8.5 |
| Tue | 8:30 AM | 6:00 PM | 30 | 9.0 |
| Wed | 9:00 AM | 6:30 PM | 60 | 8.5 |
| Thu | 8:00 AM | 7:00 PM | 30 | 10.5 |
| Fri | 8:30 AM | 5:00 PM | 30 | 8.0 |
| Sat | 9:00 AM | 2:00 PM | 0 | 5.0 |
A night row with 10:00 PM in, 6:00 AM out and no break returns 8.
3. Shade the weekend
Select A2:G8, open Format > Conditional formatting, and under "Format cells if" choose Custom formula is:
=WEEKDAY($A2, 2)>5
With 2 as the second argument, Monday is 1, so 6 and 7 are Saturday and Sunday. The $ before A applies the shading to the whole row.
4. Regular hours, overtime and pay
Put your numbers in column J and the labels in column I: J1 hourly rate (22.5), J2 overtime threshold in hours per week (40) and J3 overtime multiplier (1.5). Treat the 40 and the 1.5 as placeholders and enter the rule your employer uses. Then:
J5: =SUM(G2:G8)
J6: =MIN(J5, J2)
J7: =MAX(0, J5-J2)
J8: =J1*(J6+J7*J3)
J9: =TEXT(J5/24, "[h]:mm")
- J5 is 49.5, J6 is 40 and J7 is 9.5.
- J8 is 22.5 x (40 + 9.5 x 1.5) = 22.5 x 54.25 = 1,220.625, which shows as $1,220.63. It is an estimate for your own records, before taxes, not payroll.
- J9 shows 49:30. The [h] in the format keeps counting past 24 hours, where a plain time format would wrap back to zero.
5. Two-week pay periods
For a two-week period, do not add both weeks and compare the total with 80. Judge each week against the weekly threshold, then add the overtime. With the week totals in K1 and K2:
=MAX(0, K1-J2) + MAX(0, K2-J2)
If week 1 is 49.5 hours and week 2 is 38, that returns 9.5 + 0 = 9.5 overtime hours. Judging the 87.5-hour total against 80 would give 7.5. Which method applies depends on your employer's rule, so use the one that matches it.
6. A five-minute weekly routine
- Type the new Monday in A2 and clear D2:F8.
- Fill in times as the week goes, one row per day.
- On the last day, read J5 to J9 before you submit anything.
If you want it ready-made
There is a free timesheet template with Weekly and Biweekly sheets, a Settings sheet with a rounding step and a Dashboard that splits regular and overtime hours. It comes as a Google Sheets copy. The formulas above are enough to build your own.
How do you record shifts that cross midnight today: one row with a MOD formula, or split across two rows?
Top comments (0)