Excel shows an incorrect value when a time calculation exceeds 24 hours. This happens because Excel’s default time format resets after 24 hours. You can display the correct total by using a custom number format. This article explains why the error occurs and how to fix it with the [h]:mm format.
Key Takeaways: Fixing Time Display Over 24 Hours
- Custom Format [h]:mm: Displays total elapsed hours correctly without resetting at 24.
- Format Cells Dialog (Ctrl+1): Apply the custom time format to any cell containing a time value.
- Time Calculation Basis: Excel stores times as fractions of a day, where 1 equals 24 hours.
Why Excel Resets Time Displays After 24 Hours
Excel’s standard time formats are designed for clock times, not duration tracking. Formats like “h:mm AM/PM” or “hh:mm” follow a 12 or 24-hour clock cycle. When a calculation’s result passes 24 hours, Excel continues to calculate correctly but displays the remainder. For example, 30 hours displays as 6:00 because the format shows 30 minus 24.
This behavior is based on how Excel stores time. Excel treats one full day as the number 1. Therefore, one hour is stored as 1/24, or approximately 0.04167. A value of 1.25 represents 30 hours (1 day + 6 hours). The default formats only show the fractional day part, not the integer day part. The [h]:mm format instructs Excel to include the integer days, converted to hours, in the display.
Steps to Apply the [h]:mm Custom Format
- Select the problem cell or range
Click on the cell showing the wrong time value. To fix multiple cells, click and drag to select them all. - Open the Format Cells dialog
Press Ctrl+1 on your keyboard. You can also right-click the selected cells and choose Format Cells from the menu. - Navigate to the Custom category
In the Format Cells dialog box, click the Number tab. Select Custom from the category list on the left. - Enter the custom format code
In the Type field, delete any existing code. Type[h]:mmexactly as shown, including the square brackets. Click OK to apply the format.
Using the Format Painter for Consistency
- Format one cell correctly
First, apply the [h]:mm format to one cell using the steps above. - Activate the Format Painter
With the correctly formatted cell selected, go to the Home tab on the ribbon. Click the Format Painter icon in the Clipboard group. - Apply the format to other cells
Your cursor will change to a paintbrush. Click and drag over the other cells that need the same fix. The format will be copied instantly.
Common Mistakes and Formatting Limits
Square Brackets Are Missing or Incorrect
Using “h:mm” without square brackets will cause the display to reset at 24 hours. The square brackets around the ‘h’ are the specific instruction to Excel to accumulate hours beyond 24. Always verify the format code in the Type field reads [h]:mm and not a similar variant.
Cell Contains a Text Value, Not a Time
If applying [h]:mm does not change the display, the cell might contain text. Check by selecting the cell and looking at the formula bar. A true time value will show as a decimal or a standard time. If it’s text, you must convert it. One method is to multiply the cell by 1 using a formula like =A1*1 in a new cell, then copy and paste the result as values.
Need to Display Days, Hours, and Minutes
The [h]:mm format shows total hours. For very long durations, you may want to separate days. Use the custom format d "days" h:mm. This will display the integer day portion, the word “days”, and then the remaining hours and minutes. For 54 hours, this displays as “2 days 6:00”.
Time Format Comparison: Standard vs. Custom
| Item | Standard Format (h:mm) | Custom Format ([h]:mm) |
|---|---|---|
| Display for 30:15 (30 hrs, 15 min) | 6:15 | 30:15 |
| Primary Use Case | Clock time of day | Elapsed time or duration |
| Handles values >= 24 hours | No, resets display | Yes, shows total |
| Format Code Syntax | h:mm or hh:mm | [h]:mm or [hh]:mm |
| Underlying Cell Value | 1.2604 (for 30:15) | 1.2604 (value unchanged) |
You can now track project hours or equipment runtime accurately in Excel. Use the [h]:mm format on any cell where you sum time values. For related time calculations, explore the TEXT function to format time within a formula. An advanced tip is to combine the custom format with conditional formatting to highlight any total exceeding a specific threshold, like 40 hours.