Why does an overnight time difference show ##### or a negative number?
Formula (Excel and Google Sheets)
=MOD(C2-B2,1)The end time is "earlier" than the start time. Wrap the subtraction in MOD to roll it into the next day: =MOD(C2-B2,1).
- Result on the example
- one result per row in
D2, filled down toD6 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in D2, filled down to D6. Change any input and the result updates, or edit the formula to experiment.
How it works
Times are fractions of a day, so 6:00 AM minus 10:00 PM is negative. Excel can't display a negative time and shows #####. MOD(…,1) wraps a negative difference into the next day, so -16 hours becomes 8 hours, while shifts within one day are unchanged.
=MOD(C2-B2,1)Result0.3541666667
C25:30 PMThe value in C2.
B29:00 AMThe value in B2.
C2-B20.3541666667The - operator subtracts.
MOD(C2-B2,1)0.3541666667MOD: Remainder after division.- number
C2-B20.3541666667 - divisor
1
- number
In Google Sheets
The same formula works in Google Sheets: =MOD(C2-B2,1). We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =IF(C2<B2,C2+1-B2,C2-B2) | 8:30, 8:00, 8:00… | Adds a day when the end time is earlier than the start time. |
| =C2-B2+(C2<B2) | 8:30, 8:00, 8:00… | (C2<B2) is 1 when the shift crosses midnight, adding one day. |
Common mistakes
- This works for shifts under 24 hours. For longer spans, record full dates with times and subtract those.
- Format the result as h:mm. For pay, multiply by 24 to get decimal hours.
- Times typed as text ("10 PM " with a stray space) give #VALUE!. Check them with ISNUMBER.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Why does an overnight time difference show ##### or a negative number
D2, calculate the length of the shift, including shifts that end after midnight. Fill down to D6.Questions
How do I get decimal hours?
=MOD(C2-B2,1)*24 returns 8 for an 8-hour shift.
How do I subtract an unpaid break?
=MOD(C2-B2,1)-TIME(0,30,0) takes off 30 minutes.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.