Formatting Issues

How to Format Excel Time Durations as Total Hours and Minutes

Jason Miller Oct 19, 2026 868 views

Question details

The user needs to format spreadsheet cells to correctly display accumulated time durations, specifically to show total elapsed hours and minutes when the sum exceeds a 24-hour period.

How to Format Excel Time Durations as Total Hours and Minutes

Product: Excel
Device & OS: not provided
Scenario: Adding up multiple time durations, such as employee work hours or project times, and displaying the correct grand total.
Observed behavior: Without the correct formatting, time totals exceeding 24 hours reset like a standard clock (e.g., 25 hours displays as 1:00). The user needs to apply a custom format to show the true continuous elapsed time.

Before you start

Ensure that your duration data is entered in a recognized time format, such as 8:30 for 8 hours and 30 minutes, rather than as standard decimal numbers.

Solution 1Recommended

Apply Custom [h]:mm Format to Display Total Elapsed Time

Use the custom [h]:mm number format to prevent time totals from resetting after 24 hours, ensuring your total elapsed duration is displayed accurately.

By default, standard time formats in spreadsheet applications are designed to show time of day, meaning they roll over after reaching 24 hours. To track cumulative hours, such as total work shifts, you must apply a custom number format.

Enter and sum time values

Input each duration as a standard time value (for example, 8:30). Add the durations together using the SUM function, such as =SUM(B2:B10).

Open Format Cells

Select the cell containing your SUM formula. Right-click on the cell and choose 'Format Cells' from the drop-down menu.

Apply the custom format

In the Format Cells dialog, go to the 'Number' tab and select 'Custom' from the category list. In the 'Type' field, enter [h]:mm and click OK to apply the changes.

Apply Custom [h]:mm Format to Display Total Elapsed Time

Understanding the format: The square brackets around the 'h' instruct the software to display the value as an elapsed duration rather than a rolling clock time.

Solution 2

Calculate Elapsed Time for Overnight Shifts

If your time durations involve shifts that cross midnight, calculate the elapsed time accurately using specific formulas before summing and formatting.

Seamless Spreadsheet Editing

Calculate and Format Time Durations Easily with WPS Spreadsheet

WPS Spreadsheet provides seamless support for all advanced custom number formats, including the [h]:mm elapsed time format. You can easily track total work hours, calculate payroll data, and format your sheets perfectly using its intuitive interface.

1. Open your spreadsheet: Launch WPS Spreadsheet and open the document containing your time logs.
2. Select your total cells: Highlight the cells where your time durations have been summed up using formulas.
3. Apply the custom format: Right-click the selection, click 'Format Cells', navigate to 'Custom', and input [h]:mm to instantly display your correct total hours.

Fully compatible with Microsoft Excel time formats and complex formulasIntuitive Format Cells dialog with rich custom formatting optionsLightweight, free-to-use software for efficient daily data processing

microsoft office alternative - wps office

Frequently Asked Questions

Why does my 25-hour total show up as 1:00?

By default, spreadsheets use a standard clock time format (h:mm) which resets every 24 hours. Because 25 hours is exactly 1 hour past a 24-hour cycle, it displays as 1:00. Using the custom format [h]:mm prevents this reset and displays the full 25:00.

Can I also show total seconds in the elapsed time?

Yes. You can easily modify the custom formatting rule. Open the Format Cells dialog, select Custom, and enter [h]:mm:ss to display total elapsed hours, minutes, and seconds.

How do I multiply my total hours by an hourly pay rate?

Spreadsheet software stores time as a fraction of a 24-hour day. To calculate wages, you must multiply your [h]:mm formatted total by 24 to convert it to a decimal number, and then multiply that result by your hourly rate.

Document & File Issues