How to calculate a time difference that crosses midnight
✓ Verified in LibreOffice 25.8.7.3 ✓ Verified in Google Sheets (2026-08-30)Shift durations where clock-out is the next day — night shifts, overnight processes — without a negative result.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel (desktop) | =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. We then ran the same formulas in Google Sheets, executed 2026-08-30: a formula-only workbook goes into Google Drive, which converts it to a Sheet and recalculates every formula with Google’s own engine, and comes back out as .xlsx carrying the values Google computed. It returned 8:00 for the worked example, the same value LibreOffice produced. Both engines’ numbers on this page are executed results. The Excel formula follows Microsoft’s official documented syntax — we do not run desktop Excel.
Functions used
MOD — see full Excel, Google Sheets & LibreOffice compatibility for each.
Related recipes
- How to convert minutes to hours and minutes
- How to find the next occurrence of a weekday (next Friday)
- How to sum every nth row
- How to count weeks and days between two dates