Sum Time Past 24 Hours ([h]:mm)

Excel Formulas › Date & Time

All versionsTime format

Add up a week of hours and Excel quietly rolls past midnight — 30 hours shows as 6:00. The fix isn’t a formula, it’s a number format: square brackets [h]:mm let hours accumulate beyond 24.

Quick formula: sum a column of durations in B2:B6, then format the total cell:
=SUM(B2:B6) // then format the cell as [h]:mm
Without the brackets, the total wraps every 24 hours. With [h]:mm it shows the true cumulative hours and minutes.

Functions used (tap for the full reference guide):

SUMTEXT

The example

Five daily durations that total more than a full day.

AB
1DayHours
2Mon8:30
3Tue7:45
4Wed9:15
5Thu6:30
6Total32:00

The formula

The formula is an ordinary SUM — the magic is the format:

=SUM(B2:B5) Format cell: [h]:mm → 32:00 // not 8:00

How it works

Excel stores time as a fraction of a day, so totals need a format that doesn’t reset at 24h:

  1. Each duration is a fraction: 8:30 = 8.5/24 of a day. SUM adds the fractions correctly.
  2. A normal h:mm format only shows the time-of-day part, dropping whole days — so 32 hours displays as 8:00.
  3. The square brackets in [h]:mm tell Excel “don’t roll over” — show all accumulated hours.
  4. Apply it via Format Cells → Custom and type [h]:mm (or [mm]:ss for minutes:seconds).

Need a decimal for payroll? Multiply the total by 24: =SUM(B2:B5)*24 formatted as a number gives 32.0 hours — ready to multiply by an hourly rate.

Try it: interactive demo

Live demo

Enter daily hours (h:mm), one per line.

Total [h:mm]:   Decimal:

Variations

Decimal hours for pay

Convert the day-fraction to hours:

=SUM(B2:B5)*24

Minutes:seconds

For stopwatch-style totals:

Format: [mm]:ss

As text label

Embed the total in a sentence:

=TEXT(SUM(B2:B5), "[h]:mm") & " worked"

Pitfalls & errors

Missing brackets = wrong total. Plain h:mm wraps at 24 hours, so 32:00 shows as 8:00. Always use [h]:mm for sums.

Negative times show as ######. If an end time is before a start time (crossing midnight), the result goes negative and Excel can’t display it in time format. Add 1 to the end time when it crosses midnight.

Text entries don’t add. Hours typed as text (e.g. with a trailing space) are ignored by SUM. Make sure entries are real time values, right-aligned by default.

Practice workbook

📊
Download the free Sum Time Past 24 Hours ([h]:mm) practice workbook
A time-totaling sheet pre-formatted with [h]:mm, the decimal-hours and TEXT variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I add up hours past 24 in Excel?
Use a normal =SUM(range), then format the total cell as [h]:mm. The square brackets let hours accumulate beyond 24 instead of wrapping.
How do I convert a time total to decimal hours?
Multiply by 24: =SUM(range)*24 gives decimal hours (e.g. 32.0) you can multiply by an hourly rate.
Why does my time total show the wrong number?
A plain h:mm format resets every 24 hours. Switch the format to [h]:mm so the full elapsed time shows.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Time difference · Timesheet overtime · Seconds to h:mm:ss

Function references: SUM · TEXT