How to calculate hours worked minus a break
✓ Verified in LibreOffice 25.8.7.3Timesheet math: end time minus start time minus the unpaid break, as decimal hours for pay.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =(C2-B2-D2)*24 | B2=start, C2=end, D2=break duration (as a time like 0:30). Format the result as a Number. |
| Google Sheets | =(C2-B2-D2)*24 | Identical. |
| LibreOffice Calc | =(C2-B2-D2)*24 | Identical. |
How it works
Times are day-fractions, so (end − start − break) gives the worked portion of a day, and ×24 converts it to decimal hours: 9:00 to 17:00 minus a 30-minute lunch is 7.5 hours — the number you multiply by an hourly rate. Format the result cell as Number (it inherits time formatting otherwise). For overnight shifts wrap the difference in MOD(C2-B2,1) so crossing midnight doesn't go negative.
Verified, not just documented
We ran =(TIME(17,0,0)-TIME(9,0,0)-TIME(0,30,0))*24 in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 7.5 — exactly the expected result. Every formula here is confirmed by actually executing it.