How to calculate a time difference that crosses midnight
✓ Verified in LibreOffice 25.8.7.3Shift durations where clock-out is the next day — night shifts, overnight processes — without a negative result.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =MOD(B2-A2,1) | A = start time, B = end time. MOD wraps the negative overnight difference into the right positive value. |
| Google Sheets | =MOD(B2-A2,1) | Identical. |
| LibreOffice Calc | =MOD(B2-A2,1) | Identical. |
How it works
When the end time is earlier on the clock than the start (22:00 to 06:00), a plain B2-A2 goes negative and shows ###### or a wrong value, because the spreadsheet doesn't know a day rolled over. MOD(B2-A2,1) fixes it: MOD wraps the negative fraction back into the 0-1 day range, giving the correct 8 hours. It works for same-day shifts too (a positive difference is unchanged), so you can use MOD everywhere and stop special-casing overnight rows. Multiply by 24 for decimal hours. If shifts can exceed 24 hours, store real date-times instead and just subtract.
Verified, not just documented
We ran =TEXT(MOD(TIME(6,0,0)-TIME(22,0,0),1),"H:MM") in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 8:00 — exactly the expected result. Every formula here is confirmed by actually executing it.