Calculation Issues

How to Fix AutoSum Errors When Adding Time in Excel

Phi Hung Vo Sep 29, 2026 870 views

Question details

The user needs to accurately calculate the total of multiple time values in Excel without triggering incorrect sums or formatting errors when the result exceeds 24 hours.

How to Fix AutoSum Errors When Adding Time in Excel

Product: Excel
Device & OS: not provided
Scenario: Adding up time-based logs, work shift hours, or timesheets where the cumulative duration surpasses a 24-hour threshold.
Observed behavior: AutoSum returns an incorrect total because Excel inherently stores time as fractions of a day and resets the displayed time back to zero upon reaching 24 hours.

Before you start

Verify that your time entries are correctly recognized by Excel as numbers (time values) and not stored as text strings, as AutoSum ignores text.

Solution 1Recommended

Apply a Custom Time Format for Totals Exceeding 24 Hours

Use a custom time format with bracketed hours to prevent the sum from rolling over and resetting to zero after 24 hours.

Excel stores time values as fractions of a day, meaning 1.0 equals 24 hours. Because of this, standard time formats only display time within a single day. When summing times that total more than 24 hours, you must adjust the cell formatting to display cumulative elapsed time.

Select the time range

Highlight all the cells containing the individual time entries you want to sum, including the empty cell where the final total will be displayed.

Open the Format Cells dialog

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

Set the custom time format

Navigate to the 'Number' tab, choose 'Custom' from the category list on the left, and type '[h]:mm:ss' into the 'Type' input box. Click OK to apply.

Calculate the total

Click the designated total cell and use the AutoSum button on the Home tab, or manually type the formula (e.g., =SUM(A1:A14)) and press Enter.

Apply a Custom Time Format for Totals Exceeding 24 Hours

Formatting Tip: The square brackets around the 'h' in the [h]:mm:ss format instruct Excel to display the elapsed time as a continuous value, effectively overriding the default 24-hour limit.

Easy Time Calculation

Calculate Elapsed Time Flawlessly with WPS Spreadsheet

WPS Spreadsheet provides intuitive and robust formatting options to handle time calculations effortlessly. Easily sum up hours beyond the 24-hour mark using custom cell formatting while keeping your document fully compatible with Excel.

1. Open your timesheet: Launch WPS Spreadsheet and open your existing time-tracking document or Excel workbook.
2. Format the total cell: Highlight the cell designated for the total, right-click to select 'Format Cells', choose 'Custom', and input '[h]:mm:ss'.
3. Apply AutoSum: Click the AutoSum button on the Formula tab to accurately calculate and display the total accumulated hours.

Seamlessly sum time values exceeding 24 hours without manual conversions or formula errors.Fully compatible with Microsoft Excel's .xlsx formats and custom formatting codes.Lightweight, fast, and completely free alternative for reliable data analysis and timesheet management.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Excel time sum show a decimal number like 1.5 instead of hours?

If your total displays as a decimal like 1.5, the cell is currently formatted as 'General' or 'Number' instead of 'Time'. Because Excel stores 24 hours as 1.0, 1.5 represents 36 hours. To fix this, right-click the cell, select 'Format Cells', and apply the custom format '[h]:mm:ss' to see 36:00:00.

How do I subtract time in Excel without getting a #VALUE error?

Ensure both cells are correctly formatted as time values. Always subtract the earlier time from the later time (e.g., =B2-A2). If you are calculating a shift that crosses midnight (which results in a negative number error), use the MOD function: =MOD(B2-A2, 1).

Can I use AutoSum for minutes and seconds only without converting to hours?

Yes. You can sum just minutes and seconds by applying the custom format '[m]:ss' to the cell containing the total. The square brackets ensure that the minutes will continue accumulating and will not roll over into hours once they exceed 60.

Excel & Data Problems