how to sum time formula in this format?

Question

Im having difficulty summing a column of time in hh:mm:ss format. not sure what I am doing wrong. Here are the custom formats I have tried: custom, h:mm:ss, hh:mm:ss, [h]:mm:ss, [hh]:mm:ss. It just keeps populating as 00:00:00 when i attempt to sum. Please help.

Answer 1

Hi,

Try this formula

=sum(--I2:I25)

Format this cell as [hh]:mm

Hope this helps.

Answer 2

Dear @Melanie Donnelly,

Thank you for reaching out to Microsoft Q&A forum.

From your screenshot, it seems that Excel is treating your time entries as text strings rather than numeric values. The values like "31:56:29" are often left-aligned when Excel doesn't recognize them as numbers. When the =SUM function is applied to a range of text values, Excel treats each cell as having a value of zero, which is why you're seeing the 00:00:00 total in your spreadsheet.

The issue is further complicated by entries like 31:56:29, which exceed 24 hours. By default, Excel may fail to recognize durations greater than 24 hours as numbers during an import, categorizing them as text instead.

To fix this, you need to force Excel to recalculate these text strings as numeric time values. Here’s how to proceed:

1. Highlight the column containing the time (e.g., Column I).
2. Go to the Data tab and select Text to Columns.
3. Choose Delimited and click Next.
4. Uncheck all delimiters and click Next.
5. On the final screen, ensure the Column data format is set to General and click Finish.
6. Excel will attempt to re-evaluate the entries as numeric time values.

If the above does not work, you can force the conversion using a simple calculation:

1. In an empty cell, type the number 1 and copy it (Ctrl + C).
2. Select your time data range.
3. Right-click and select Paste Special.
4. Choose Multiply and click OK.
5. This often forces Excel to convert text-based digits into numbers.

Excel usually “rolls over” hours after 24, meaning 25:00:00 will display as 01:00:00. To show the cumulative total without this reset:

1. Go to the Home tab > Number > Number Format
2. Select Custom at the bottom of the list.
3. Type the following code exactly:
   [h]:mm:ss
4. The square brackets [h] tell Excel to keep counting hours past 24 instead of resetting to zero.

I hope this information is helpful. Please follow these steps and let me know if it works for you. If not, we can work together to resolve this. Thank you for your patience and understanding. If you have any questions or need further assistance, please feel free to share them in the comments so I can continue to support you. I'm looking forward to your reply.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.