How to Display Total Hours Greater Than 24 in Excel
Question details
The user needs to correctly display the sum of time values that exceed 24 hours without the total resetting to a daily clock value.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Summing multiple cells containing elapsed time to calculate total hours worked or duration.
- Observed behavior
- When the sum exceeds 24 hours, the standard h:mm format resets and only displays the remaining hours after full days are subtracted (e.g., 40 hours displays as 16 hours).
Verify that your original time entries are formatted correctly as time values (e.g., 12:00) and not as plain text, otherwise they cannot be mathematically summed.
Apply Custom Number Format for Elapsed Hours
Use the bracketed hour format to force Excel to display total cumulative hours instead of the default 24-hour clock time.
By default, standard time formats in spreadsheets represent a specific time of day, which cycles every 24 hours. Wrapping the hour symbol in square brackets tells the program to calculate the absolute cumulative hours, preventing the rollover effect.
Highlight the cell or range of cells containing the summed total time that currently displays the incorrect hour value.
Right-click the selected cell and choose 'Format Cells' from the context menu, or press the 'Ctrl + 1' keyboard shortcut.
In the Format Cells window, go to the 'Number' tab and click on 'Custom' at the bottom of the Category list.
In the 'Type' text box, enter [hh]:mm (or [hh]:mm:ss if you need to track seconds) and click 'OK' to apply the new format.
Calculate and Format Elapsed Time in WPS Spreadsheet
WPS Spreadsheet fully supports advanced custom number formatting, allowing you to sum and track time over 24 hours effortlessly. As a powerful data analysis tool, it handles complex calculations with full Excel compatibility.
- 1. Open your timesheet: Launch WPS Spreadsheet and open your existing .xlsx time tracking file.
- 2. Access cell formatting: Select your total time cell, right-click, and select 'Format Cells', or press Ctrl+1.
- 3. Apply cumulative format: Choose the 'Custom' category, type [hh]:mm in the input box, and hit OK to display total hours perfectly.
Frequently Asked Questions
Why does my sum of hours show up as a decimal number like 1.66?
This happens if the cell format is accidentally set to 'General' or 'Number' instead of 'Time'. In spreadsheet logic, 1 represents a full 24-hour day. Applying the [hh]:mm custom format will convert the decimal back into cumulative elapsed hours.
Can I format total minutes exceeding 60 in the same way?
Yes. You can track cumulative minutes without them rolling over into hours by placing square brackets around the minute symbol. Simply apply the custom format [mm]:ss to your total cell.
Does applying the [hh]:mm format change the underlying cell data?
No, changing the format only alters how the data is visually displayed. The underlying value remains a fraction of a 24-hour day, allowing you to continue performing accurate mathematical calculations, such as multiplying the time by an hourly wage.
