Excel Sum of Time Over 24 Hours — Why Your Total Resets (and the [h]:mm Fix)

TL;DR — =SUM(times) adds your durations correctly — it's the format that lies. The default h:mm shows only the remainder after whole days, so a true total of 25 hours 30 minutes displays as 1:30. The number underneath is right; apply the custom format [h]:mm and it shows 25:30. The square brackets mean "let this unit accumulate instead of rolling over at 24." Two companions: a negative time shows #######, and SUM(times) * 24 gives you the total as decimal hours.

=SUM(B2:B8)        ' true total 25:30 ... but displays 1:30 with default h:mm
' Fix: Format Cells -> Custom -> type   [h]:mm      -> shows 25:30
=SUM(B2:B8) * 24   ' -> 25.5   (decimal hours, no wrapping at all)

You total a week of shifts and the answer is obviously wrong — eight-hour days that sum to 1:30. Nothing is broken: Excel added the fractions perfectly and then the clock format threw away the whole days before showing you the result. This is the most common time complaint in Excel, and it's a display problem with a one-setting fix. This guide covers why it happens, the [h]:mm format, the ####### cousin, and when a decimal total serves you better.

What you'll learn

Why SUM is correct but the total displays wrong
The [h]:mm custom format and what the square brackets mean
Why a negative time shows ####### — and three fixes
Getting the total as decimal hours instead
A time-format cheat sheet (and the m = month vs. minute trap)

Why the total resets: the format hides whole days

A time is a fraction of a day, and SUM adds those fractions with no problem. Twenty-five and a half hours is the number 25.5 / 24 = 1.0625 — one full day plus 0.0625 of the next. The default h:mm format is a clock format: it shows the time of day, which means it displays only the 0.0625 left over after the whole day and discards the rest.

' B2:B8 are seven shifts that really total 25 hours 30 minutes
=SUM(B2:B8)     ' underlying value 1.0625  ->  displays 1:30 in default h:mm

So the 1:30 you see is "1:30 on the clock the next day." The sum is correct to the second; the format is throwing away the days. Fixing the display fixes the "wrong" total.

The fix: the [h]:mm elapsed-time format

Select the total, open Format Cells → Number → Custom, and enter:

[h]:mm

The square brackets are the whole trick. [h] means "show the total hours, do not roll over at 24." Without brackets, h is a clock hour and wraps; with brackets, it accumulates:

' Same underlying value 1.0625
' Format  h:mm    -> 1:30      (clock, wrapped)
' Format  [h]:mm  -> 25:30     (elapsed, correct)
' Format  [m]     -> 1530      (total minutes)
' Format  [m]:ss  -> 1530:00   (total minutes and seconds, e.g. for a stopwatch)

Put the brackets on the largest unit you want to accumulate: [h] to let hours pile up, [m] if you want the whole thing in minutes, [s] for seconds. It's a number format only — it changes nothing about the stored value, so the cell still feeds further formulas cleanly.

The #### cousin: negative times

The same fraction model explains Excel's other notorious time symptom. Subtract a later value from an earlier one — or let a total go below zero — and the cell fills with #######. Excel's standard 1900 date system has no representation for a negative time, so instead of a minus sign it shows the overflow hashes:

=A2 - B2      ' if B2 is later than A2  ->  #######  (negative time)

Three fixes, in order of preference:

Order the subtraction correctly, or use =MOD(B2 - A2, 1) for an overnight span so the result is never negative — see converting time to decimal.
Work in decimal hours with * 24: a decimal can be negative, so =(A2 - B2) * 24 shows -1.5 instead of hashes — useful for "over/under" columns.
As a last resort, switch the workbook to the 1904 date system (File → Options → Advanced), which permits negative times — but it shifts every existing date by four years, so avoid it unless the file is built around it.

Getting a decimal total instead

Often the cleaner answer isn't a bracketed time at all — it's a plain number. Multiply the sum by 24 and format the cell as Number:

=SUM(B2:B8) * 24     ' -> 25.5    (decimal hours; never wraps, easy to multiply)
=SUM(B2:B8) * 24 * rate          ' straight to a labour cost

Decimal hours don't have a 24-hour ceiling and multiply cleanly by a pay rate, so for anything downstream of the total — cost, average, comparison — prefer the number over the [h]:mm display.

The time-format cheat sheet

h:mm — clock time, wraps at 24. For a time of day, not a duration.
[h]:mm — elapsed hours and minutes, accumulates. For durations.
h:mm AM/PM — 12-hour clock.
[m]:ss — total minutes and seconds (stopwatch / lap times).
h:mm:ss.000 — with milliseconds.

The classic trap: in a number format, m means minutes right after h or hh, but month everywhere else. mm on its own is months; hh:mm is hours and minutes; if you need a month-and-minute mix, know that Excel disambiguates by position. When a "minutes" format mysteriously shows a small number like 07, you've written a month code.

The judgment call

A durations column that must read correctly → format it [h]:mm once; never leave a duration in the default h:mm, or it will silently wrap for any total ≥ 24h.
A total feeding cost, average, or a chart → convert to decimal hours with * 24 and store a number, not a time.
A cell showing ####### → it's a negative time; fix the subtraction order or move to decimal hours before reaching for the 1904 system.
Payroll math → decimal hours, always; see convert time to decimal.

How ExcelMaster helps

The maddening part of this bug is that the formula is already correct — only the format betrays it, so people rewrite working SUMs trying to fix a display setting. ExcelMaster spots the pattern (a time total that wrapped, a ####### cell, a duration in a clock format) and applies the right number format or the * 24 conversion, so the total you read is the total Excel computed. You get the answer, not a debugging session over a hidden fraction.

Frequently asked questions

Why does my time total reset after 24 hours?

The value is correct; the format is a clock format (h:mm) that shows only the remainder after whole days, so 25:30 displays as 1:30. Apply the custom format [h]:mm — the brackets tell Excel to accumulate hours instead of wrapping at 24.

What does [h]:mm mean in Excel?

It's a custom number format for elapsed time. The square brackets on [h] let the hours run past 24 instead of rolling over, so a duration of 25 hours 30 minutes shows as 25:30 rather than 1:30. Use [m] or [s] to accumulate in minutes or seconds.

Why does my time show ####### (pound signs)?

The cell holds a negative time — usually a later time subtracted from an earlier one — and Excel's 1900 date system can't display negative times. Fix the subtraction order, use =MOD(end - start, 1), or work in decimal hours (* 24), which can be negative.

How do I sum a week of hours worked?

=SUM(range) and format the total [h]:mm, or use =SUM(range) * 24 for a decimal total you can multiply by a pay rate. Both add the same underlying value; only the display differs.

How do I show a time total as a decimal number?

Multiply by 24 and format the cell as Number: =SUM(range) * 24 turns 25:30 into 25.5. Because it's a plain number it never wraps at 24 and multiplies cleanly by a rate.

Tested in

Tested in: Excel 365 (Windows 11) — last verified 2026-07-25.