Date & Time
Calculate hours between two times (including overnight night shifts)
Wrap EndTime - StartTime in MOD(..., 1) * 24 so shifts crossing midnight return 8.5 positive hours instead of -15.5.
Why EndTime - StartTime turns negative on night shifts
Clock times without dates store fractions of a 24-hour day: 22:00 is 0.9167 and 06:30 is 0.2708. Subtracting 0.2708 - 0.9167 yields -0.6458 (-15.5 hours)!
Because floored modulo MOD(-0.6458, 1) adds 1 full day (+1.0) whenever the difference is negative while leaving positive daytime differences unchanged, MOD(End - Start, 1) * 24 handles both day shifts and overnight shifts with one formula.
Equal start/end clocks return zero, not a 24-hour shift. This clock-only example does not account for daylight-saving transitions; use full timestamps with a defined time zone when actual elapsed time matters.
| ShiftStart (A) | ShiftEnd (B) | Naive (B-A)*24 | MOD(B-A, 1)*24 |
|---|---|---|---|
| 22:00 | 06:30 | -15.5 (wrong!) | 8.5 |
| 09:15 | 17:45 | 8.5 | 8.5 |
Overnight-safe elapsed hours formula
From 22:00 to 06:30 is 2 hours before midnight plus 6.5 hours after midnight = 8.5 hours.
=ROUND(MOD(B2 - A2, 1) * 24, 2)Compare daytime shift and naive subtraction
Daytime 09:15 to 17:45 also returns 8.5 hours, while naive (B2 - A2) * 24 on row 2 exposes -15.5.
=ROUND((B2 - A2) * 24, 2)Try the example
Use the original values or change them to test your understanding.