Excel Time Format Over 24 Hours: Display Elapsed Time Correctly

To display a duration longer than 24 hours in Excel, select the cells, open Format Cells, choose Custom, and enter [h]:mm or [h]:mm:ss. The square brackets make Excel show total elapsed hours instead of resetting the hour display to zero after each day.

Why Excel time rolls over at 24 hours

Excel stores dates and times as numbers: the whole-number portion represents days, and the fractional portion represents part of a day. For example, 0.5 represents 12 hours, while 1 represents 24 hours. A duration of 36 hours is stored as 1.5 days.

A standard time format such as h:mm displays the time of day. Once the stored value reaches a full day, the display cycles back to midnight. That is useful for a clock reading, but misleading when a cell represents hours worked, a project duration, or the total time spent on several tasks. The value has not necessarily been lost; the format is showing the hour within a 24-hour cycle rather than the total hours.

The brackets in [h] tell Excel to display the cumulative number of hours. A value of 1.5 days therefore appears as 36:00 with [h]:mm, not 12:00. The same principle applies to total minutes and seconds: [m]:ss shows cumulative minutes, while [s] shows cumulative seconds.

Apply a total-hours format in Excel

In Excel for Microsoft 365 on Windows, apply the format to the cells containing the duration or total:

Select the relevant cells. You can select an entire column if future entries should use the same display.
Press Ctrl+1 to open the Format Cells dialog.
On the Number tab, choose Custom.
Enter [h]:mm in the Type box for hours and minutes, or [h]:mm:ss to include seconds.
Select OK and check that the total now displays the expected elapsed hours.

You can also reach number-format options through Home > Number Format drop-down > More Number Formats. On Excel for Mac, select the cells and press Command+1 to open Format Cells; then choose Custom and enter the format. Excel for the web can display and apply many custom number formats, but if the controls you need are unavailable, open the workbook in the desktop app to create or edit the format.

Choose the format that matches the precision you need. Use [h]:mm for a timesheet total such as 38:45, and [h]:mm:ss when seconds matter, such as a recorded process duration. The brackets are essential: h:mm is a clock-style format and will not show accumulated hours beyond 23.

Understand what the displayed value means

Changing a number format changes how a value appears, not the underlying value. A duration displayed as 36:00 is still the numeric value 1.5, measured in Excel days. This matters when you use the cell in calculations: Excel can add, subtract, and compare the duration as a number even though the screen shows hours and minutes.

It also explains why formatting alone cannot convert text into a duration. If a cell contains text characters that look like 36:00, a custom number format may have no effect because Excel is not holding a numeric time value. A quick clue is alignment: numbers usually align to the right by default and text to the left, though manual formatting can change alignment. Use numeric time entries or convert imported text to values before relying on calculations.

For a decimal-hours report, the underlying value needs to be converted from days to hours. With a duration in A2 equal to one day and 12 hours, enter =A2*24 in another cell; the result is 36. Format the result as General or Number if you want a decimal number rather than a time display. Keep the original duration if you also need minute-level detail.

Calculate elapsed time across dates

For a job that can last more than a day, record both the date and the time at the start and finish. This gives Excel enough information to distinguish, for example, a 36-hour interval from a 12-hour interval. In this layout, A2 contains 11/1/2026 8:00 AM and B2 contains 11/2/2026 8:30 PM. Enter =B2-A2 in C2; the result is 1.520833333 days, which displays as 36:30 when C2 uses [h]:mm.

If the cells contain only clock times, a finish time earlier than a start time could mean that the interval crossed midnight. For a single overnight shift shorter than 24 hours, with the start time in A2 and finish time in B2, enter =MOD(B2-A2,1). For example, 10:00 PM to 6:00 AM returns 8:00 when formatted as [h]:mm. This method assumes the interval is less than one day; time-only entries cannot identify how many full days passed.

For intervals that may exceed 24 hours, use full date-and-time entries and subtract the start from the finish. Otherwise a formula designed to wrap a clock time across midnight can hide whole days. Check that the finish timestamp is later than the start timestamp; a negative result usually signals reversed entries or an incorrect date.

Add durations without losing the total

Excel can sum time values normally, but the cell showing the answer must also use an elapsed-time format. Suppose C2:C4 contain three numeric durations: 8:00, 9:30, and 7:15. Enter =SUM(C2:C4) in C5. The result is 24:45, or 24 hours and 45 minutes, when C5 is formatted as [h]:mm. With the usual h:mm format, the same total may appear as 0:45, making it look as though nearly a day has disappeared.

Format both the individual durations and the total consistently so readers can compare them easily. If you are building a report, you can apply the custom format to the whole duration column and its total row. Remember that a displayed value of 24:45 is 24 hours and 45 minutes, not a time of day or a decimal number of hours.

Entering and importing durations

A duration must be a numeric value for arithmetic to work. Excel commonly recognizes time entries such as 8:30 as a time value, but a time entry by itself describes a position within a day, not necessarily a duration of eight hours and 30 minutes. For totals that may exceed a day, the display format and the way the data is entered both matter.

For an unambiguous numeric entry, enter a decimal number of days and then apply the elapsed-time format. For example, 1.5 days displays as 36:00 under [h]:mm.
If you import durations from a text file or copy them from another system, confirm that Excel recognizes them as numbers. Text can look exactly like a time but fail in SUM or subtraction.
For start and finish records, include dates as well as times whenever a shift may cross midnight or last more than a day.
Keep the cell format consistent across the column. A correct value can still look wrong if one cell uses a clock format and the others use an elapsed-time format.

When a source provides decimal hours, such as 7.5 for seven and a half hours, do not simply apply [h]:mm to 7.5. Excel interprets 7.5 as 7.5 days, which is 180 hours. Convert decimal hours to a fraction of a day by dividing by 24, or keep the source as decimal hours and format it as a number. Choose one representation for a column rather than mixing decimal hours and Excel time values.

Common display problems and fixes

The total shows 0:30 instead of 24:30: Change the total cell from h:mm to [h]:mm. The value may already be correct.
The format has no effect: Check whether the entries are text. Convert them to numeric values before applying a time format.
The total seems far too large: Check whether decimal hours were entered as though they were days. A numeric 8 is eight days to Excel, not eight hours.
The result is negative or displays as hashes: Check the order and dates of the start and finish entries. In the standard 1900 date system, negative time values are not displayed as ordinary times.
Minutes appear confusing: In a custom format, mm means minutes when placed in a time context, such as after the colon in [h]:mm. Use [m] when you want total minutes rather than minutes within each hour.

Also distinguish a total-hours format from a 24-hour clock format. A format such as HH:mm uses a 24-hour clock for time of day, but it still wraps after 23:59. The brackets, as in [h]:mm, are what tell Excel to accumulate hours across days.

Choose the right format for the task

Use [h]:mm for most work logs, shift totals, and project durations. It is compact and makes the number of whole hours easy to read. Use [h]:mm:ss when seconds are meaningful. Use [m]:ss for a long recording or process where total minutes are more useful than hours. Use a decimal number instead when a report or downstream calculation expects hours such as 36.5.

Do not use a clock format merely because the data contains a colon. A start time of 8:30 AM is a time of day and ordinarily belongs in a clock format such as h:mm AM/PM. A duration of eight hours and 30 minutes belongs in an elapsed-time format such as [h]:mm. Keeping those meanings separate makes calculations, chart labels, and reports much less error-prone.

Frequently Asked Questions

What is the Excel format for more than 24 hours?
Use [h]:mm for total hours and minutes, or [h]:mm:ss to include seconds. Apply it through Format Cells > Custom.

Why does Excel show 0:00 after a 24-hour total?
The cell is likely using a clock-style format such as h:mm, which wraps around each day. The underlying total may still be intact; display it with [h]:mm.

Can Excel calculate a 36-hour shift?
Yes. Store start and finish as full date-and-time values, subtract the start from the finish, and format the result as [h]:mm. Time-only values cannot tell Excel how many complete days passed.

How do I show total minutes instead of total hours?
Apply the custom format [m]:ss to show cumulative minutes and seconds. For example, 90 minutes appears as 90:00 rather than 1:30.

Does changing the format convert the value to hours?
No. Formatting changes only the display. Excel still stores a time duration as a fraction or multiple of a day; multiply a duration by 24 if you need its numeric value in decimal hours.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.