# How to calculate time worked across midnight in Excel

Wrap the difference in MOD: `=MOD(C2-B2,1)*24`. A negative overnight difference becomes the right number of hours.

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

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

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Nurse | Start | End | Hours |
| Rosa | 0.9166666666666666 | 0.2708333333333333 |  |
| Tom | 0.7916666666666666 | 0.2916666666666667 |  |
| Jin | 0.9791666666666666 | 0.3020833333333333 |  |
| Kate | 0.2916666666666667 | 0.6458333333333334 |  |
| Omar | 0.90625 | 0.20833333333333334 |  |
| Lucy | 0.5833333333333334 | 0.9583333333333334 |  |

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

Result: 8.50; 12.00; 7.75; 8.50; 7.25; 9.00

## How it works

A shift from 10:00 PM to 6:30 AM gives a negative difference, -0.65 of a day. `MOD(…,1)` wraps negative values into the next day, so it becomes 0.35, which is 8.5 hours. Day shifts are unchanged, because MOD leaves values between 0 and 1 alone.

## Other ways

- `=(C2-B2+(C2<B2))*24`: Adds one day when the end time is earlier than the start; the same result.
- `=MOD(C2-B2,1)`: The duration as a time. Format the cell as h:mm to show 8:30. (A fraction of a day.)

## Common mistakes

- Plain `C2-B2` on a night shift is negative, and Excel shows #### for a negative time.
- MOD assumes the shift is shorter than 24 hours. For longer spans, record the dates too and subtract the full date-times.

## Questions

**How do I total a week of night shifts?**

Sum the hours column, or `=SUMPRODUCT(MOD(C2:C7-B2:B7,1))*24`.

**How do I show 8:30 instead of 8.5?**

Leave out *24 and format the cell as `h:mm`.

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/time-worked-across-midnight