How to sum hours and time
Use =SUM(B2:B20) as normal, then format the total cell as [h]:mm rather than h:mm. The square brackets tell Excel and Google Sheets to keep counting past 24 hours instead of rolling over — without them, 25 hours displays as 1:00. To get a decimal number of hours instead, multiply the total by 24.
Total a column of durations
=SUM(B2:B20)Then format the result as [h]:mm. The formula is never the problem; the format is.
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Why your total resets at 24 hours
A time in a spreadsheet is a fraction of a day: 6:00 is 0.25, 12:00 is 0.5. Adding times therefore adds fractions, and the sum is correct — the display is what betrays you.
The standard h:mm format shows only the part of the value within a single day, so 1.0417 (25 hours) renders as 1:00. Nothing has been lost; the cell still holds the right number.
The [h] format code means "total hours, do not wrap". Applying it to the total cell is the entire fix. In Google Sheets the same code lives under Format → Number → Custom number format.
Check the underlying value
=SUM(B2:B20)*24If this returns 25 while the cell shows 1:00, the format is the only thing wrong.
Timesheets: hours worked from start and end times
Subtracting a start time from an end time gives the duration directly. The complication is a shift that crosses midnight, where the end time is numerically smaller than the start and the subtraction goes negative.
Adding 1 (one whole day) when the end is earlier than the start resolves it without needing dates in the cells.
Hours worked in a day
=C2-B2Shift crossing midnight
=IF(C2<B2,C2+1-B2,C2-B2)MOD does the same in one step: =MOD(C2-B2,1)
Less an unpaid break in minutes
=MOD(C2-B2,1)-TIME(0,D2,0)Weekly total, formatted [h]:mm
=SUM(E2:E8)Converting between time and decimal hours
Payroll wants decimal hours; the sheet holds day fractions. Multiplying by 24 converts one to the other, and dividing by 24 goes back.
Round with care. Rounding decimal hours to two places is fine for display but will not reconcile against a total computed from unrounded values — decide which one is authoritative before anyone queries the difference.
Time to decimal hours
=B2*24Decimal hours to a time value
=B2/24Format the result as [h]:mm.
Pay from hours worked
=B2*24*$E$1E1 is the hourly rate. Without the *24 you pay for a fraction of a day.
Correct, but depends on values from your own sheet, so we cannot run it here.
Round to the nearest 15 minutes
=MROUND(B2,TIME(0,15,0))Summing hours between two dates
Totalling a duration column only for rows inside a date window is a SUMIFS job, with the same two rules that catch people elsewhere: the sum range comes first, and an operator concatenated to a cell reference needs an ampersand.
If the date column carries a time component, an inclusive upper bound will miss most of the final day. Bracket with ">= start" and "< end + 1" instead.
Hours logged between two dates
=SUMIFS(C:C,A:A,">="&E1,A:A,"<="&E2)Format the result as [h]:mm.
Correct, but depends on values from your own sheet, so we cannot run it here.
When the date column holds date-times
=SUMIFS(C:C,A:A,">="&E1,A:A,"<"&E2+1)Correct, but depends on values from your own sheet, so we cannot run it here.
Hours for one person in a date range
=SUMIFS(C:C,A:A,">="&E1,A:A,"<="&E2,B:B,E3)Correct, but depends on values from your own sheet, so we cannot run it here.
Total in decimal hours
=SUMIFS(C:C,A:A,">="&E1,A:A,"<="&E2)*24Correct, but depends on values from your own sheet, so we cannot run it here.
Negative durations
Excel cannot display a negative time under the default 1900 date system — the cell fills with ####. Google Sheets shows negative durations without complaint.
Working in decimal hours sidesteps the problem entirely, which is usually the right call for any sheet that computes variances against a target.
Variance against target, in decimal hours
=(C2-B2)*24-8Plain number, so a negative shows as -1.5 rather than ####.
Variance as text, keeping the h:mm look
=IF(C2<B2,"-","")&TEXT(ABS(C2-B2),"[h]:mm")Time format codes
| Format | Shows 25 hours as | Use for |
|---|---|---|
| h:mm | 1:00 | A clock time |
| [h]:mm | 25:00 | A total duration |
| [m] | 1500 | Total minutes |
| [s] | 90000 | Total seconds |
| General (×24) | 25 | Decimal hours for payroll |
Frequently asked
Why does my hours total reset after 24 hours?
The cell format, not the formula. h:mm only displays the part of the value within one day. Change the total cell to the custom format [h]:mm and it will keep counting.
How do I convert a time to decimal hours?
Multiply by 24. A time is stored as a fraction of a day, so 6:00 is 0.25 and 0.25 × 24 = 6.
How do I calculate hours across midnight?
Use =MOD(C2-B2,1), or =IF(C2<B2,C2+1-B2,C2-B2). Both add a whole day when the end time is numerically earlier than the start.
Why does my time cell show ####?
Excel cannot display a negative time value. Either widen the column if the value is positive, or work in decimal hours by multiplying by 24 so negatives can display.
Does [h]:mm work in Google Sheets?
Yes — Format → Number → Custom number format, then enter [h]:mm. Google Sheets also displays negative durations, which Excel will not.
Stop writing these by hand
Describe what you need in plain English and FormulaCraft returns the formula, already run through a real spreadsheet engine. Free, no account.
Related
Last reviewed 2026-08-23. Every formula here is executed against sample data by our verification engine as part of the build. The few that cannot be — because they need values from your own sheet, or because the engine has no implementation for that form — say so directly underneath, rather than being counted as verified.