The formula
Duration (h:mm) (fill down)ExcelGoogle Sheets
=MOD(B2-A2,1)In E2 → 8:30
Decimal hours minus break (fill down)ExcelGoogle Sheets
=MOD(B2-A2,1)*24-C2/60In F2 → 8.00
✓ Tested. All 4 Excel results in this example were recalculated in LibreOffice Calc 25.2 and matched the values shown.
Worked example
Paste the data below into a blank sheet starting at cell A1 (or download the cheat sheet, where every example is set up). Highlighted cells hold the formulas and show the expected results.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Start | End | Break (min) | |||
| 2 | 22:00 | 6:30 | 30 | 8:30 | 8.00 | |
| 3 | 9:00 | 17:45 | 45 | 8:45 | 8.00 |
How it works
- B2-A2
- End minus start, as a fraction of a day.
- MOD(…,1)
- Adds a day when the result is negative (overnight).
- *24
- Converts a day fraction to hours.
- C2/60
- Break minutes as hours.
Common errors and fixes
Why does the formula return ######?
A negative time. Use MOD.
Total over 24h resets?
Format totals as [h]:mm instead of h:mm.
Does this work in Google Sheets?
Yes. The formulas on this page work the same way in Google Sheets.