How to Sum Excel Times Beyond 24 Hours
Question details
The user needs to calculate the sum of time durations that exceed 24 hours without the total resetting like a standard 24-hour clock.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating total hours worked, project durations, or accumulated time where the sum goes beyond a single day.
- Observed behavior
- Time totals over 24 hours (such as 26:00) wrap around and display as a smaller number (like 2:00) because standard time formats reset after a full day.
Ensure your time entries are recognized as valid time values rather than plain text before applying the SUM function.
Use Custom Time Formatting to Display Accumulated Hours
Apply the [h]:mm custom format so the spreadsheet displays total accumulated hours instead of resetting the clock.
Standard time formats in spreadsheets use a 24-hour clock (hh:mm). When the sum of your time exceeds 24 hours, the program resets to zero, making 26 hours display as 2:00. To fix this, you must wrap the hour placeholder in square brackets to tell the system to accumulate the hours.
Select the cell or range containing your summed time totals.
Right-click the selected cell and choose 'Format Cells' from the context menu, or press the Ctrl+1 shortcut on your keyboard.
In the Format Cells dialog, go to the 'Number' tab and select 'Custom' from the Category list on the left.
In the 'Type' input box, enter [h]:mm or [hh]:mm:ss depending on whether you want to display seconds. Click OK to apply.

Sum and Format Time Easily in WPS Spreadsheet
WPS Spreadsheet fully supports advanced custom cell formatting, making it simple to track working hours, calculate project durations, and sum times beyond 24 hours effortlessly.
- 1. Open timesheet: Open your schedule or timesheet document in WPS Spreadsheet.
- 2. Sum the data: Use the =SUM() function to add up the cells containing your time entries.
- 3. Access Format Cells: Right-click the result cell, select 'Format Cells', and go to the Custom category.
- 4. Set custom time: Type [h]:mm into the Type box and hit OK to display the accumulated hours over 24.

Frequently Asked Questions
Why does my sum of times show 0:00 when I know the total is exactly 24 hours?
A standard time format resets every 24 hours. Exactly 24 hours represents a full day, showing as 0:00 on the standard 24-hour clock. Changing the cell format to [h]:mm fixes this by preventing the reset.
How can I multiply my total accumulated hours by an hourly wage?
Spreadsheets store time as a fraction of a day. To calculate wages, multiply your accumulated time cell by 24 to convert it to a standard decimal number, and then multiply that result by the hourly rate.
Does the [h]:mm custom format work for negative times?
By default, spreadsheets display error hashes (######) for negative times. To show negative times, you either need to change your workbook to use the 1904 date system in the advanced options, or use formulas to calculate the absolute difference and append a minus sign as text.




