Show Time Over 24 Hours in Excel: Formatting Guide

To display time values that exceed 24 hours in Excel, you must apply a custom number format that includes square brackets around the hour code, like [h]:mm. Without the brackets, Excel resets the display to 0 at 24 hours, even though the underlying value is correct. This guide explains the formatting, the math behind it, and common pitfalls.

Why Excel Resets at 24 Hours

Excel stores dates and times as numbers. A full day equals 1, so one hour equals 1/24, and one minute equals 1/1440. When you format a cell with the standard h:mm, Excel shows the time of day, which cycles from 0:00 to 23:59. For example, 30 hours stored as 1.25 days displays as 6:00 with h:mm, because 1.25 days is 6:00 AM of the next day. The value is still correct; only the display is misleading.

The square-bracket syntax [h] tells Excel to accumulate hours beyond 24 instead of rolling over. The same works for minutes ([m]) and seconds ([s]). This is the only way to show elapsed time that exceeds one day without converting to a decimal number.

How to Apply the Custom Format

Follow these steps to format a cell or range so it shows total hours over 24:

1. Select the cell or range containing your time values.
2. Press Ctrl+1 (or right-click and choose Format Cells).
3. Go to the Number tab, then select Custom at the bottom of the category list.
4. In the Type box, delete the existing code and type [h]:mm (or [h]:mm:ss for seconds).
5. Click OK.

For example, if you have 1 day, 2 hours, and 30 minutes (stored as 1.10417), the cell will now show 26:30 instead of 2:30. If you later add another 2 hours, the display updates to 28:30.

Formula Behind the Display

The underlying value is a fraction of a day. To convert that to hours, multiply by 24: =A1*24 gives decimal hours (e.g., 1.10417 × 24 = 26.5). To get minutes, multiply by 1440. The custom format does this conversion internally for display, but the cell value remains unchanged. If you need to use the hours in another calculation, either use the decimal value or the HOUR, MINUTE, and SECOND functions, but note that HOUR returns the hour within a day (0–23), not the total. For total hours, use =INT(A1*24) for whole hours and =MOD(A1*24,1)*60 for minutes.

Common Mistakes and How to Avoid Them

• Forgetting the brackets: h:mm resets at 24; [h]:mm does not. This is the most frequent error.
• Using TEXT with wrong format: =TEXT(A1,"h:mm") also resets. Use "[h]:mm" inside TEXT to get the accumulated hours.
• Negative time values: If you subtract a larger time from a smaller one, Excel may show #####. Switch to the 1904 date system (File > Options > Advanced > “Use 1904 date systemâ€�) to display negative times, but be aware this shifts all dates by 4 years.
• Copying formats: When you copy a cell with a custom format, the format travels with it, which is usually fine, but if you paste values only, the format is lost.

When to Use Decimal Hours Instead

Sometimes you need a number you can multiply by an hourly rate or add directly. In that case, convert to decimal hours with =A1*24 and format as a number. For instance, 26:30 becomes 26.5. This is easier for payroll or billing because you avoid the 24-hour display confusion. However, if you need to show the time in a readable format for a report, the [h]:mm custom format is clearer. You can also use both: keep the original time value in one column and a decimal-hours column for calculations.

Working with Durations Longer Than 24 Hours

When adding or summing times, Excel’s SUM function works correctly on the underlying values. For example, summing 8:00, 9:30, and 10:15 gives 27:45. The display will show 3:45 unless you apply [h]:mm to the sum cell. The same applies to averages: =AVERAGE(A1:A10) returns an average time, but if the average exceeds 24 hours (rare but possible), you need the same format. For durations that span multiple days, you can also include days in the format, like d "days" h:mm, but that shows the day count as a number, not accumulated hours. If you want both, use [h]:mm and optionally add a separate column with =INT(A1) for days.

Frequently Asked Questions

Why does my time show as #####?

The ##### appears when the column is too narrow to display the formatted value. Widen the column or change the format to a shorter one like [h]:mm instead of [h]:mm:ss. If the value is negative, Excel also shows ##### by default; switch to the 1904 date system to see negative times.

Can I use [h]:mm with times that include seconds?

Yes, use [h]:mm:ss to show hours, minutes, and seconds. The brackets work the same way for all three units. For example, 26 hours, 30 minutes, and 15 seconds displays as 26:30:15.

Does the 1904 date system affect my existing dates?

Yes, switching to the 1904 date system changes the serial number for every date in the workbook, shifting dates by 4 years. This can corrupt existing date data. Only use it if you need negative time display and you are prepared to adjust dates, or if you are working with files from a Mac that already use 1904.

How do I convert a duration to total minutes?

Multiply the time value by 1440 (the number of minutes in a day). For example, if A1 contains 26:30, =A1*1440 gives 1590. Format the result as a number, not as a time.

Why does the HOUR function return 2 for 26:30?

The HOUR function returns the hour component of the time of day, which is always between 0 and 23. For 26:30, the time of day is 2:30 AM, so HOUR returns 2. To get total hours, use =INT(A1*24) instead.

Related

• Word Processor File Formats Explained
• TypeScript for Loops: How to Stop with Break?
• Lazy Load in WordPress: Keep Hero Image Fast

in t | Total minutes | min | 1500 | 0
out h | Hours (decimal) | h | t / 60 | 2
out hm | Hours:Minutes | h:mm | t / 60 | 0
out hms | Hours:Minutes:Seconds | h:mm:ss | t / 60 | 0