Date & Time
Timesheet formula with breaks: calculate net hours including overnight shifts
Use MOD(End - Start, 1) * 24 - (BreakMinutes / 60) so day shifts and midnight-crossing night shifts both calculate accurately.
Why naive (End - Start) * 24 breaks on night shifts
On a daytime shift from 08:30 to 17:00, (End - Start) * 24 equals +8.5 hours. On an overnight hospital or warehouse shift from 22:00 to 06:30, 06:30 is numerically smaller than 22:00, so End - Start is negative (-15.5 hours)!
Wrapping the difference in MOD(End - Start, 1) adds 1 full day whenever End < Start, turning -15.5/24 into +8.5/24 automatically while leaving daytime shifts untouched.
This clock-only pattern assumes a shift shorter than 24 hours and does not account for daylight-saving changes. For longer shifts use full start/end dates and times, and subtract them without MOD. Equal start and end clocks mean zero here, not a full-day shift.
| Shift (A) | Start (B) | End (C) | BreakMin (D) | Net Hours |
|---|---|---|---|---|
| Day Shift | 08:30 | 17:00 | 30 | 8.00 |
| Night Shift | 22:00 | 06:30 | 30 | 8.00 |
Midnight-safe net payroll hours formula
Multiply MOD(C2 - B2, 1) by 24 to get gross decimal hours (8.5), then subtract BreakMin / 60 (0.5) = 8.00 net hours.
=ROUND(MOD(C2 - B2, 1) * 24 - (D2 / 60), 2)Guard excessive break entries
MAX(0, ...) can floor the displayed result, but it hides excessive-break mistakes. Validate that break minutes are nonnegative and no greater than the shift length, and flag invalid entries for correction before using the result.
=MAX(0, ROUND(MOD(C3 - B3, 1) * 24 - (D3 / 60), 2))Try the example
Use the original values or change them to test your understanding.