Formatting Issues

How to Display Cumulative Hours in Excel Using the [h]:mm Format

Natalie Taylor Nov 2, 2026 869 views

Question details

The user needs to display total elapsed time in hours and minutes without the value resetting to a standard clock time after surpassing 24 hours.

How to Display Cumulative Hours in Excel Using the [h]:mm Format

Product: Excel
Device & OS: not provided
Scenario: Calculating or displaying total elapsed time, such as summing up hours for weekly timesheets or project tracking.
Observed behavior: Excel defaults to displaying time values as a 24-hour clock. When elapsed time exceeds 24 hours (e.g., 30:30), it resets and displays the remainder as a time of day (e.g., 6:30).

Before you start

Ensure your time data is entered properly in a time format (e.g., using a colon between hours and minutes) and that you have selected all the relevant cells before modifying their formatting.

Solution 1Recommended

Apply the Custom [h]:mm Time Format

Use square brackets around the hour symbol in the custom number format to force Excel to display total elapsed hours instead of the standard time of day.

When you add times together that exceed a total of 24 hours, Excel naturally rolls over to the next day and only displays the remaining hours as a clock value. By applying the custom format [h]:mm, you instruct the software to accumulate the hours.

This custom format is essential for timesheets, payroll calculations, and tracking elapsed project times.

Select the target cells

Highlight the cells containing the elapsed time or the sum formula that you want to format.

Open the Format Cells dialog box

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

Choose the Custom category

Navigate to the 'Number' tab, then select 'Custom' from the Category list on the left side of the window.

Enter the cumulative format

In the 'Type' text box, type [h]:mm and click 'OK' to apply the new format to your selected cells.

Formula Bar Display: The formula bar may still display the underlying date-time serial value (for example, 30 hours and 30 minutes may show as 1/1/1900 6:30:00 AM), but the worksheet cell will correctly display the cumulative hours as 30:30.

Track Time Easily with WPS Spreadsheet

Calculate Cumulative Hours Effortlessly in WPS Spreadsheet

WPS Spreadsheet provides robust custom formatting tools identical to Microsoft Excel, making it easy to track project hours, calculate payroll, and sum elapsed time without formatting errors.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your time-tracking or payroll workbook.
2. Select the cells to format: Click and drag to highlight the cells containing your time sums or elapsed hours.
3. Access Cell Formatting: Right-click the selection and click 'Format Cells', or use the 'Ctrl + 1' shortcut.
4. Apply Custom Format: Navigate to the 'Custom' category, type [h]:mm in the Type input field, and click 'OK'.

Fully compatible with Microsoft Excel (.xlsx) formats and formulasSupports all standard and custom time formatting rules, including [h]:mmFree to download and use with a highly familiar user interfaceLightweight application that opens large data files rapidly

microsoft office alternative - wps office

Frequently Asked Questions

Why does my sum of hours display incorrectly in Excel?

By default, Excel formats time values as a 24-hour clock. If your total exceeds 24 hours, it rolls over to a new day and only displays the remainder. Applying the [h]:mm custom format fixes this by forcing the display of cumulative hours.

Can I display cumulative seconds or minutes in Excel?

Yes, you can display cumulative minutes or seconds by using square brackets around the respective time code in the Format Cells dialog. For example, use [m]:ss to show total elapsed minutes or [s] for total elapsed seconds.

How do I calculate total pay based on cumulative hours?

After summing your cumulative hours using the [h]:mm format, multiply that time cell by 24 to convert it into a standard decimal number (e.g., 30.5 hours). Then, multiply that decimal by the hourly pay rate. Ensure your final result cell is formatted as Currency or General, not as Time.

Why does the formula bar show a date like 1/1/1900 instead of my hours?

Excel stores dates and times as sequential serial numbers where 1 whole day equals the number 1. Therefore, 30 hours is stored as 1.25 days, which translates to January 1, 1900, at 6:00 AM in Excel's underlying calendar system. The [h]:mm format changes the visual output in the cell, but the underlying serial value remains visible in the formula bar.

Document & File Issues