Excel Days, Hours, and Minutes: Format Durations

To show an elapsed time in Excel as days, hours, and minutes, keep the duration as a number and either format it as total hours with [h]:mm or use a formula to display separate day, hour, and minute parts. For a positive duration in A2, for example, =INT(A2)&" days "&HOUR(A2)&" hours "&MINUTE(A2)&" minutes" displays the components as text.

How Excel stores elapsed time

Excel represents dates and times as numbers. A whole number represents a day, and the decimal part represents a fraction of a day: 1 is 24 hours, 0.5 is 12 hours, and 1/1440 is one minute. That underlying value is why a duration can be calculated, added, or subtracted just like other numbers.

A duration of 2 days, 3 hours, and 15 minutes is stored as 2 + 3/24 + 15/1440, or 2.1354167 when displayed to seven decimal places. Applying a display format changes how the value looks, not the value Excel uses in calculations. This distinction matters: a cell showing 51:15 can still contain a numeric duration that you can total or compare.

Elapsed time is different from a clock time. A clock time such as 3:15 PM identifies a point within a day. A duration such as 51 hours and 15 minutes measures an interval and can exceed 24 hours. Formats designed for clock times may wrap at midnight; elapsed-time formats and formulas avoid that when used appropriately.

Show a duration as total hours

For many timesheets and project totals, a display such as 51:15 is the clearest option: it means 51 hours and 15 minutes, not 2:15 on a clock. Select the duration cells, open Format Cells with Ctrl+1 on Windows, choose Number > Custom, and enter [h]:mm in the Type box. On Mac, Command+1 opens Format Cells; the same custom format is available. In Excel for the web, use the number-format controls to choose or enter a custom format if that option is available in your current interface; if it is not, use desktop Excel to set it.

The square brackets around h are essential. They tell Excel to show cumulative hours rather than resetting the hours after each 24-hour day. For a duration stored as 2.1354167 days, [h]:mm displays 51:15. By contrast, h:mm displays only the hour and minute within the day, so it shows 3:15 for the same value. Add seconds with [h]:mm:ss when the underlying data includes seconds that matter.

Use this approach when the total number of hours is the main result. It preserves a numeric value, so a cell formatted as [h]:mm can still be summed. If a report specifically needs separate day, hour, and minute labels, use component calculations instead.

Display separate days, hours, and minutes

With a positive elapsed duration in A2, use this formula to create a readable text label:

=INT(A2)&" days "&HOUR(A2)&" hours "&MINUTE(A2)&" minutes"

For example, if A2 contains 2 days, 3 hours, and 15 minutes, the result is “2 days 3 hours 15 minutes.” INT returns the whole-day part; HOUR and MINUTE return the remaining hour and minute portions within the day. The result is text rather than a duration, so use the original numeric cell—not the label—for totals, sorting by elapsed time, or further calculations.

This formula does not show seconds. If seconds are present, they remain in the underlying duration but are left out of the label. Decide whether to display seconds, round the source value first, or deliberately omit them. Also, if you want singular wording such as “1 day” rather than “1 days,” the simple formula will not adjust grammar; a more elaborate formula or a separate presentation step is needed.

To convert a positive whole-number count of minutes in A2 into text components, use:

=QUOTIENT(A2,1440)&" days "&QUOTIENT(MOD(A2,1440),60)&" hours "&MOD(A2,60)&" minutes"

If A2 is 1501, the result is “1 days 1 hours 1 minutes”: 1501 minutes is 24 hours and 1 minute. This version is useful when the source is a count of minutes rather than an Excel time value. It assumes whole, nonnegative minutes; decimal minutes and negative values need an explicit rounding or sign-handling decision.

Calculate a duration from start and end times

If A2 contains a start date and time and B2 contains an end date and time, with both full date-time values entered, calculate the elapsed duration with:

=B2-A2

For example, a start of November 2 at 9:00 AM and an end of November 4 at 12:15 PM produces 2 days, 3 hours, and 15 minutes. Apply [h]:mm to show 51:15, or use the separate-component formula above to make a text label. Including dates is important when an interval crosses midnight or lasts more than one day.

If A2 and B2 contain clock times only, with no dates, and the end time is later on the same day, subtraction also works. For a shift that crosses midnight, use a wraparound calculation instead:

=MOD(B2-A2,1)

For example, a start time of 10:30 PM in A2 and an end time of 2:15 AM in B2 gives 3:45. This method assumes the interval is less than 24 hours. It cannot tell whether the times represent a four-hour overnight shift or a longer interval spanning one or more full days; record dates or use a separate day count for that.

Convert durations to decimal hours or days

Some systems request decimal hours rather than hours and minutes. With an Excel duration in A2, multiply by 24:

=A2*24

If A2 is 1 day and 6 hours, its value is 1.25 days, so the result is 30 hours. Format the result as Number and choose the decimal places needed for the task. A duration of 1 hour and 30 minutes becomes 1.5 hours, not 1.30 hours; minutes are fractions of an hour, not digits after a decimal point.

To express the same duration in days, leave the underlying value unchanged or divide a total-hours value by 24. For reporting, a numeric day value such as 1.25 is often easier to calculate with than a text label. Confirm the receiving system’s convention before converting: payroll, billing, and scheduling systems may require different rounding rules or units.

Common formatting and calculation problems

The total resets after 24 hours: Replace h:mm with [h]:mm. The ordinary h code shows the hour within a day; the bracketed code accumulates elapsed hours.
The cell displays a decimal: The duration may be correct but formatted as General or Number. Apply a time format such as [h]:mm, or use the component formula if the report needs words.
A formula returns a number or a date: Excel stores date-times and durations as serial numbers. Check the cell’s number format and whether the formula returns a numeric value or concatenated text.
A total looks too large or too small: Check whether the source values are minutes, decimal hours, or Excel day fractions. Multiplying a value already expressed in hours by 24, for instance, gives the wrong unit.
A duration is negative: Subtracting a later start from an earlier end can produce a negative value. Excel’s default 1900 date system may display negative date/time values as hash marks. Check the date order and, for overnight clock-only times under 24 hours, consider the MOD approach above rather than changing the workbook’s date system.
Minutes appear as months: In custom date formats, m can mean month or minute depending on context. In a time format it is interpreted as minutes next to hour or second codes; for a standalone label, calculate the minute component with MINUTE or MOD instead.

Choose a method that fits the result

Use [h]:mm when people need a compact, numeric total they can add and compare. Use separate day, hour, and minute components when the report needs an explicit breakdown. Use decimal hours when another calculation or system expects a fraction such as 1.5 hours. These representations describe the same underlying duration but are not interchangeable in every workflow: the component label is text, while the other two can remain numeric.

For a timesheet with individual work periods, calculate each duration from its start and end, then sum the numeric results and format the total with [h]:mm. If shifts can cross midnight, include dates or use a clearly stated under-24-hour assumption. Keep the raw start and end values and the calculated duration in separate columns; that makes it easier to audit a result and correct a mistaken entry without parsing a text label.

Frequently Asked Questions

How do I show more than 24 hours in Excel?
Format the duration cell as [h]:mm, or [h]:mm:ss if seconds are needed. The brackets make Excel display cumulative hours instead of wrapping at 24.

How do I convert Excel time to days, hours, and minutes?
For a positive duration in A2, use the component formula in the section above. Excel stores each whole day as 1 and each hour as 1/24, so INT, HOUR, and MINUTE can separate the parts. The resulting label is text.

Why does Excel show 1.5 instead of 36 hours?
Excel stores 36 hours as 1.5 days. The value is not wrong; format the cell as [h]:mm to display 36:00, or multiply the numeric value by 24 to calculate decimal hours.

Can Excel calculate a shift that ends after midnight?
Yes. Full start and end date-times can be subtracted directly. If the cells contain clock times only and the shift is under 24 hours, MOD(end minus start, 1) calculates the overnight interval; clock times alone cannot identify a longer span.

How do I add durations without losing hours after a day?
Sum the numeric duration cells, then format the total as [h]:mm. The format changes the display, not the sum, and prevents the displayed hours from wrapping at 24.

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.