How to sum times past 24 hours (without them wrapping)
✓ Verified in LibreOffice 25.8.7.3 ✓ Verified in Google Sheets (2026-08-30)Total hours across many rows — and stop the total resetting to zero after 24.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel (desktop) | =SUM(A2:A10) | The formula is fine — format the result cell as [h]:mm (square brackets stop the 24-hour wrap). |
| Google Sheets | =SUM(A2:A10) | Identical; Format → Number → Duration does the same thing. |
| LibreOffice Calc | =SUM(A2:A10) | Identical; format code [HH]:MM. |
How it works
20:00 plus 10:30 is 30 and a half hours, but a cell formatted h:mm shows 6:30 — the display wraps at 24 like a clock, even though the underlying value (1.27 days) is correct. Bracketed hour codes ([h]:mm) tell the app to keep counting instead of wrapping: 30:30. So the fix is formatting, not the SUM. For payroll math, multiply the total by 24 to get decimal hours instead.
Verified, not just documented
We ran =TEXT(TIME(20,0,0)+TIME(10,30,0),"[H]:MM") in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 30:30 — exactly the expected result. We then ran the same formulas in Google Sheets, executed 2026-08-30: a formula-only workbook goes into Google Drive, which converts it to a Sheet and recalculates every formula with Google’s own engine, and comes back out as .xlsx carrying the values Google computed. It returned 30:30 for the worked example, the same value LibreOffice produced. Both engines’ numbers on this page are executed results. The Excel formula follows Microsoft’s official documented syntax — we do not run desktop Excel.
Functions used
SUM — see full Excel, Google Sheets & LibreOffice compatibility for each.
Related recipes
- How to calculate a cumulative (running) percentage
- How to calculate each row as a percentage of the total
- How to reference a cell on another sheet
- How to make a running (cumulative) total
- How to sum a column