Calculation Issues

How to Sum Excel Times Beyond 24 Hours

Steve KSteve K Nov 10, 2026 869 views

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.

How to Sum Excel Times Beyond 24 Hours
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.
Before you start

Ensure your time entries are recognized as valid time values rather than plain text before applying the SUM function.

Solution 1Recommended

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.

1
Select the target cell

Select the cell or range containing your summed time totals.

2
Open Format Cells

Right-click the selected cell and choose 'Format Cells' from the context menu, or press the Ctrl+1 shortcut on your keyboard.

3
Navigate to Custom formats

In the Format Cells dialog, go to the 'Number' tab and select 'Custom' from the Category list on the left.

4
Apply the square bracket format

In the 'Type' input box, enter [h]:mm or [hh]:mm:ss depending on whether you want to display seconds. Click OK to apply.

Use Custom Time Formatting to Display Accumulated Hours
How it works: The square brackets around the 'h' override the default time-of-day clock logic, forcing the spreadsheet to calculate the absolute accumulated time regardless of how many days it adds up to.
Easy Spreadsheets with WPS Office

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. 1. Open timesheet: Open your schedule or timesheet document in WPS Spreadsheet.
  2. 2. Sum the data: Use the =SUM() function to add up the cells containing your time entries.
  3. 3. Access Format Cells: Right-click the result cell, select 'Format Cells', and go to the Custom category.
  4. 4. Set custom time: Type [h]:mm into the Type box and hit OK to display the accumulated hours over 24.
Fully compatible with Microsoft Excel custom number formats like [h]:mm.Lightweight and fast for handling large timesheets and schedule data.Free to download and includes all essential spreadsheet functions.Familiar user interface makes it easy to switch and start working immediately.
microsoft office alternative - wps office

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.