To show an elapsed time in Excel without resetting the hours after 24, apply the custom number format [h]:mm; use [h]:mm:ss when seconds matter. Select the cells, press Ctrl+1 on Windows, choose Custom, enter the format in the Type box, and select OK. The square brackets make Excel display total elapsed hours rather than the hour on a clock.
Why Excel needs a duration format
Excel does not store a time duration as a special kind of value. It stores times as numbers: one whole day is 1, one hour is 1/24, and one minute is 1/1,440. A duration of 30 hours is therefore stored as 1.25. The cell’s number format controls how that value appears.
A regular time format such as h:mm is designed to display a time of day. Its hour component cycles through the 24-hour clock, so a value equal to 30 hours can appear as 6:00 rather than 30:00. That is useful for a clock time, but misleading for a work total, machine run time, or other measurement that can exceed one day.
The brackets in [h]:mm tell Excel to show the accumulated hours. They do not change the stored value or the result of calculations. Formatting is a display choice: a duration of 30 hours remains the same numeric value whether it appears as 6:00, 30:00, or 1.25.
Choose a format for the precision you need
Pick a format based on what readers need to see. These are common custom formats for elapsed time:
[h]:mmdisplays total hours and minutes, such as 30:15.[h]:mm:ssdisplays total hours, minutes, and seconds, such as 30:15:40.[m]:ssdisplays total minutes and seconds, such as 1,815:40 for a duration of 30 hours, 15 minutes, and 40 seconds.[s]displays total seconds, without breaking them into minutes or hours.h:mmdisplays a clock-style time, with the hour cycling through a 24-hour day. Use it for a time of day, not a cumulative total.
In a format such as [h]:mm:ss, the bracketed hours accumulate, while minutes and seconds display as parts of the current hour and minute. A total duration of 27 hours, 8 minutes, and 5 seconds appears as 27:08:05. For a report that should show days as well as hours, use a format such as d "days" h:mm, but remember that the unbracketed hour is the hour within the displayed day. If the priority is a single total, [h]:mm is usually clearer.
Apply a custom duration format
In Excel for Microsoft 365 on Windows, select the duration cells and press Ctrl+1 to open Format Cells. Choose the Number tab, select Custom in the Category list, type [h]:mm or another suitable code in the Type box, and select OK. You can also open the number-format options from the Home tab’s Number group and choose More Number Formats.
- Select the cells containing durations, or select a whole column if new entries will be added there.
- Open Format Cells with Ctrl+1.
- Choose Custom, enter the desired format code, and confirm.
- Check a value longer than 24 hours to confirm the total is displayed as expected.
The format applies to the selected cells; it does not convert text into numeric durations or alter formulas. On Excel for Mac, select the cells and use Format > Cells to open the formatting dialog, then choose Custom and enter the code. In Excel for the web, use the number-format controls on the Home tab; if the custom format you need is unavailable in the web interface, open the workbook in the desktop app to set it.
Calculate durations from start and end times
If both cells contain full date-and-time values, subtract the start from the end. With the start timestamp in A2 and the end timestamp in B2, enter =B2-A2 in C2. For example, if A2 is 9:00 AM on Monday and B2 is 11:30 AM on Tuesday, the result is 26 hours and 30 minutes; format C2 as [h]:mm to display 26:30.
If the cells contain clock times only and a shift may cross midnight, use a formula that wraps the difference to the next day. With the start time in A2 and end time in B2, enter =MOD(B2-A2,1) in C2. For a start of 10:00 PM and an end of 6:00 AM, the result is 8 hours. This approach assumes the end is later than the start on the following day when the clock time is earlier. It cannot determine whether a shift lasted more than 24 hours from clock times alone; use actual dates and times when that distinction matters.
Apply [h]:mm to calculated durations as well as manually entered values. A formula can return the right number while the cell’s default time format makes it look like a shorter clock time. For a series of durations, the same custom format can show a cumulative total after summing them.
Add durations and display totals
Excel can add duration values because they are numeric. With individual durations in C2:C10, enter =SUM(C2:C10) in C11. If the values add up to 52 hours and 45 minutes, format C11 as [h]:mm to display 52:45. With an ordinary time format, the same sum may appear as 4:45, which can look like a calculation error even though the underlying total is correct.
When a total looks wrong, check both the formula range and the number format. Confirm that every intended row is included and that the source cells contain numeric values rather than text. If the total is being used for payroll, billing, or reporting, make sure the workbook’s rounding rules match the rules for that task; changing how a duration is displayed does not round the stored value.
Convert durations to decimal hours or minutes
Some systems or reports require decimal hours instead of hours and minutes. Excel’s stored time value is a fraction of a day, so multiplying it by 24 gives decimal hours. With a duration in A2, enter =A2*24 in B2. If A2 represents 2 hours and 30 minutes, B2 returns 2.5. Format B2 as Number if you want a numeric decimal result rather than a time display.
To convert a duration to total minutes, multiply the stored value by 1,440. With the duration in A2, enter =A2*1440 in B2. A duration of 2 hours and 30 minutes returns 150. Conversely, to turn a numeric decimal-hour amount into a duration value, divide by 24. With decimal hours in A2, enter =A2/24 in B2, then apply [h]:mm. For an input of 2.5, the displayed result is 2:30.
Keep the distinction between a numeric conversion and a visual format in mind. Applying [h]:mm to a value of 2.5 displays 60:00, because Excel interprets 2.5 as two and a half days. It does not mean 2.5 hours. Use multiplication or division when the underlying unit needs to change.
Common duration-format problems
- The total resets after 24 hours: Change a clock-style format such as
h:mmto[h]:mm. The brackets are essential for cumulative hours. - The cell shows ####: Widen the column first. If the value is negative, the default 1900 date system is another possible cause; Excel does not display negative time values normally in that system.
- The formula result is a decimal: The value may be correct but formatted as General or Number. Apply an elapsed-time format to display hours and minutes.
- The format has no effect: The entry may be text, not a numeric time value. Re-enter it as a valid time or convert the source data to numeric values before formatting.
- A large total appears as a clock time: Check that the total cell itself has an elapsed format. Formatting the input cells does not always apply the same format to a formula cell.
Excel workbooks can use the 1900 or 1904 date system. Switching a workbook’s date system is not a routine fix for negative durations: it can shift the interpretation of existing dates. If negative results are meaningful, consider displaying them as signed decimal hours or using a formula that returns text for presentation, while keeping a numeric result separately for calculations. Text output is not as convenient for further arithmetic.
Frequently Asked Questions
What is the best Excel format for total hours?
Use [h]:mm for total hours and minutes, or [h]:mm:ss when seconds are needed. The brackets prevent the displayed hours from cycling back to zero after 24.
How do I show more than 24 hours in Excel?
Select the cells, open Format Cells, choose Custom, and enter [h]:mm. For example, a 30-hour duration displays as 30:00 rather than 6:00.
Why does my Excel time total show the wrong number of hours?
A standard time format may be displaying the time within a 24-hour day rather than cumulative hours. Apply an elapsed format such as [h]:mm, then verify that the formula includes all the intended cells.
Can Excel calculate a shift that ends after midnight?
Yes. If A2 contains the start clock time and B2 the end clock time, =MOD(B2-A2,1) calculates an overnight duration such as 10:00 PM to 6:00 AM as eight hours. Use full dates and times for shifts that might last 24 hours or longer.
How do I show a duration as decimal hours?
Multiply the numeric duration by 24. If A2 contains 2 hours and 30 minutes, =A2*24 returns 2.5. Format the result as a number, not as a time.
Does changing the time format change the value?
No. A number format changes only how Excel displays a value. Use a formula to convert units, or change the calculation itself, if you need a different underlying result.
Related
- Data Validation: Definition, Rules, and Examples
- Excel Data Validation Date Picker: Setup and Options
- Excel Data Validation for Dates: Rules and Examples
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Microsoft Excel Corrupt File? How to Repair ItIf an Excel file is corrupt, first make a copy of it, then try Excel for Microsoft 365…
- Excel Advanced Goal Seek: Solver, Limits, and ExamplesExcel has no separate feature called Advanced Goal Seek: the built-in Goal Seek tool finds a value for…
- Google Sheets Alphabetize a Column: A Practical GuideTo alphabetize a column in Google Sheets, select a cell in your data and choose Data > Sort…
- How to Animate in Microsoft PowerPoint: A Practical GuideTo animate in Microsoft PowerPoint, select text or an object, open the Animations tab, choose an effect, then…
- Share a Calendar in Outlook: Steps and PermissionsTo share a calendar in Outlook, open Calendar, choose the calendar you want to share, select Share or…
- Excel Freeze Panes Shortcut: Windows Keys and TipsIn Excel for Microsoft 365 on Windows, press Alt, then W, F, and F to open Freeze Panes.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.