# Why does an overnight time difference show ##### or a negative number?

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).

**Excel:** `=MOD(C2-B2,1)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Shift | Start | End | Hours worked |
| Day | 0.375 | 0.7291666666666666 |  |
| Evening | 0.625 | 0.9583333333333334 |  |
| Night | 0.9166666666666666 | 0.25 |  |
| Late night | 0.9791666666666666 | 0.3020833333333333 |  |
| Early | 0.20833333333333334 | 0.5625 |  |

Formula in D2, filled to D6: `=MOD(C2-B2,1)`

Result: 8:30; 8:00; 8:00; 7:45; 8:30

## 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.

## Other ways

- `=IF(C2<B2,C2+1-B2,C2-B2)`: Adds a day when the end time is earlier than the start time.
- `=C2-B2+(C2<B2)`: `(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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/overnight-time-difference-negative