A shift from 10 PM to 6 AM is 8 hours — but end − start goes negative across midnight. MOD wraps it correctly so overnight shifts total properly.
MOD(…, 1) wraps a negative day-fraction back into 0–1, so a 22:00–06:00 shift correctly reads 8 hours.
The example
10 PM to 6 AM = 8 hours.
| A | B | |
|---|---|---|
| 1 | Shift | Hours |
| 2 | 22:00 → 06:00 | 8.0 |
| 3 | 09:00 → 17:30 | 8.5 |
The formula
Wrap the difference with MOD:
How it works
MOD handles the midnight wrap:
- Times are day-fractions;
end − startis negative when the end is past midnight. MOD(diff, 1)wraps any negative fraction into the 0–1 range — turning−0.667into0.333(8 hours).- Multiply by 24 for decimal hours, or format the result cell as
[h]:mmfor clock time. - It works for same-day shifts too, so one formula covers both cases.
Subtract a break: =MOD(end-start, 1)*24 - breakHours. And if a shift can legitimately be 24 hours, MOD would return 0 — add a guard, since MOD treats a full day as zero.
Try it: interactive demo
Start and end (crossing midnight is fine).
Variations
As clock time
Format [h]:mm:
Minus a break
Subtract lunch:
Pay for the shift
Hours times rate:
Pitfalls & errors
Don’t use plain end−start. It goes negative across midnight and shows ###### or a wrong total. MOD fixes it.
Exactly 24 hours = 0. MOD treats a full day as zero; guard if a 24-hour shift is possible.
Date+time is even cleaner. If your cells include the date, a plain subtraction works without MOD — MOD is for time-only values.
Practice workbook
Frequently asked questions
How do I calculate hours for an overnight shift in Excel?
Why does end minus start give a negative time?
How do I subtract a break?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class