Excel Day Hour Minute Second Format: Display Durations

To display an elapsed time in Excel as hours, minutes, and seconds, apply the custom number format [h]:mm:ss. The square brackets keep the hour count running past 24; to show days as a separate number, use a formula or a separate days column, because an ordinary date-format day token does not mean total elapsed days.

Apply the elapsed-time format

In Excel for Microsoft 365 on Windows, select the duration cells, press Ctrl+1 to open Format Cells, choose the Number tab, select Custom, enter [h]:mm:ss in the Type box, and choose OK. This changes how the values look; it does not change the underlying numbers or stop Excel calculating with them. On a Mac, Command+1 opens Format Cells. In Excel for the web, number-format controls may differ, and some custom-format editing options are more limited; use the desktop app if you cannot enter the format there.

The display reads as total hours followed by minutes and seconds. For example, a value representing 1 day, 2 hours, 3 minutes, and 4 seconds appears as 26:03:04. That is often the clearest compact way to show a duration that exceeds a day.

Select the cells that contain numeric duration values.
Open Format Cells and choose Custom.
Enter [h]:mm:ss, then confirm.
Check a value longer than 24 hours to make sure the total-hour display is what you need.

You can also use the format [h]" hr "mm" min "ss" sec" to label the units. The quoted text is literal, while the bracketed hour and the minute and second placeholders remain numeric. Use the colon version for compact reports and the labeled version when readers might mistake a duration for a clock time.

How Excel represents time

Excel stores dates and times as serial numbers. In the standard 1900 date system, the whole-number part represents days and the fractional part represents a portion of a day. Thus, 0.5 represents 12 hours, and 1.25 represents one day plus six hours. A time format changes the display of that number, not its value.

This explains why time arithmetic works naturally. Adding 0.5 to a duration adds half a day, or 12 hours. It also explains a common surprise: a cell formatted as ordinary h:mm:ss shows the time-of-day portion and cycles after 24 hours. The elapsed format [h]:mm:ss instead displays the accumulated hours. Excel workbooks can use either the 1900 or 1904 date system; when workbooks using different systems are combined, date values can differ, so check the workbook’s date-system setting if dates shift. The basic duration formatting principle remains the same.

Why the brackets matter

In custom number formats, square brackets around an elapsed-time unit request a cumulative value. The format [h]:mm:ss therefore counts all hours, rather than showing only the hour within the current day. Without brackets, h:mm:ss is suited to a clock reading or a time-of-day display, and a 26-hour duration can look like 2:00:00 rather than 26:00:00.

Minutes and seconds are normally written as mm and ss to keep a leading zero: 3 minutes and 4 seconds display as 03:04. The letter m can represent either minutes or months in Excel’s date and time formats. Its interpretation depends on its position among other time codes; in [h]:mm:ss, it is read as minutes. If you create a more elaborate custom format, keep the time fields together and preview the result in the Format Cells dialog.

There is no equivalent ordinary [d] elapsed-day token to simply insert at the front and get a dependable day-hour-minute-second breakdown. A date-format d represents a calendar day component, not a total count of days elapsed. For a compact total, use bracketed hours; for an explicit day count and remainder, use a formula or divide the display into columns.

Show days, hours, minutes, and seconds separately

For a human-readable label such as “1 d 2 h 3 min 4 sec,” a formula can extract the whole days and the remaining time components. With a nonnegative numeric duration in A2, use this formula in another cell:

=INT(A2)&" d "&HOUR(A2)&" h "&MINUTE(A2)&" min "&SECOND(A2)&" sec"

If A2 contains 1.085462963 days, the result is 1 d 2 h 3 min 4 sec (assuming the stored value corresponds to exactly 1 day, 2:03:04). INT returns the whole-day portion; HOUR, MINUTE, and SECOND return the remaining clock components. This is a text result, not a numeric duration, so keep the original value in A2 for calculations, sorting, and charting. The formula is intended for nonnegative durations; negative values need a different presentation strategy.

You can also use a days column next to the elapsed-time value. For example, show whole days with =INT(A2) and format the remaining fraction in another cell with a formula such as =MOD(A2,1), then apply h:mm:ss to that remainder. With A2 holding 1 day, 2:03:04, INT returns 1 and MOD returns the fraction for 2:03:04. This keeps the day count and the time remainder in separate numeric cells, which can be useful in reports. Before each formula, confirm that A2 contains the numeric duration rather than text.

Convert seconds or minutes into an Excel duration

If a source system provides a duration as a number of seconds, divide by the number of seconds in a day. Excel’s time serials are measured in days, so the result is a true numeric duration that can be added or averaged. With the number of seconds in A2, enter:

=A2/86400

For example, if A2 is 93,784 seconds, the formula returns a duration that displays as 26:03:04 when the result cell is formatted as [h]:mm:ss. The arithmetic is 93,784 seconds = 26 hours, 3 minutes, and 4 seconds. Keep the input as a number, not text such as “93784 sec,” or the division may fail.

For a source value measured in minutes, divide by 1,440, since a day contains 1,440 minutes. If A2 contains 1,563 minutes, =A2/1440 gives 1 day and 2 hours 3 minutes, which displays as 26:03:00 with the elapsed format. If the source is hours, divide by 24. These conversions preserve a numeric result; a text label may be easier to read, but it is less useful for further arithmetic.

Choose a format for the task

Pick the display according to whether the value is a clock reading, a duration, or a count that needs an explicit day breakdown. The stored value may look similar in each case, but the intended meaning is different.

Time of day: Use h:mm AM/PM or h:mm:ss for a clock reading such as 2:30 PM. It is not a suitable display for totals that can pass midnight.
Elapsed time under a day: Use h:mm:ss when values will stay below 24 hours, or use [h]:mm:ss consistently if the range might grow.
Elapsed time over a day: Use [h]:mm:ss for a simple total, such as 49:15:00 for two days, one hour, and 15 minutes.
Days plus a remainder: Use separate numeric columns or a text-building formula when readers specifically need “2 days, 1 hour…” rather than a total-hour count.
Fixed reporting units: If a report needs decimal hours, convert the duration to hours instead of relying on a clock-style display.

For decimal hours, remember that a displayed duration is not itself a decimal number of hours. A value of 1 hour 30 minutes is 1.5 hours, not 1.30 hours. You can calculate decimal hours by multiplying the stored day value by 24. Preserve the original duration if you may later need to show hours, minutes, or seconds again.

Common errors and troubleshooting

If a total unexpectedly resets after 24 hours, check for missing brackets: h:mm:ss wraps, while [h]:mm:ss accumulates. If the cell shows a date or an unexpected month, inspect the custom format for an m token in the wrong context; m can stand for month in date formats. Resetting the cell to General can also help distinguish a value problem from a display problem.

The result stays left-aligned or will not calculate: The entry may be text. Re-enter it as a number or use a conversion formula on the numeric source.
Fractions appear in seconds: The underlying value may contain fractional seconds. Use a format with seconds to round the display, or round the value in a separate calculation if the data itself must be rounded.
Negative durations show ####: Excel’s standard 1900 date system does not display negative date/time serials as ordinary times in the usual way. Consider displaying a signed number of hours or using text for negative results instead of expecting the time format to solve it.
A displayed zero has unexpected punctuation: Review the custom format’s separators and quoted labels, and try the simpler [h]:mm:ss format first.

Formatting does not repair incorrect arithmetic. If a total is wrong, verify the source units, whether values are numeric, and whether the formula adds the intended cells. Also remember that cell formatting can hide precision: a display of 01:00:00 might contain a few fractional seconds not visible on screen.

Frequently Asked Questions

How do I display more than 24 hours in Excel?
Apply the custom format [h]:mm:ss through Format Cells. A 27-hour value then displays as 27:00:00 instead of cycling back to 3:00:00.

How do I show days, hours, minutes, and seconds in one cell?
Use a formula to join the day count and the remainder components, such as the INT, HOUR, MINUTE, and SECOND example above. The resulting label is text; retain the original duration for calculations.

Can I type a duration like 25:30:00?
Excel may interpret it as a time value, but the initial display depends on the cell’s existing format and input parsing. Apply [h]:mm:ss afterward and confirm that the stored value is numeric and represents 25 hours 30 minutes.

Why does h:mm:ss show the wrong number of hours?
Without brackets, h is a clock-style hour field and cycles at 24 hours. Use [h]:mm:ss for elapsed totals.

Does changing the format change the value?
No. A number format controls appearance only. Use a formula to convert units or change a value; use formatting to choose how that numeric value is displayed.

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.