Calculation Issues

How to Sum Elapsed Times Stored as Text in Excel

Guest Writer Oct 7, 2026 869 views

Question details

The user needs to calculate the total of elapsed times, but the SUM function fails because the time values are stored as text strings instead of recognized time serial numbers.

How to Sum Elapsed Times Stored as Text in Excel

Product: Excel
Device & OS: not provided
Scenario: Attempting to sum a column of elapsed hours and minutes that were imported, pasted, or entered in a way that formatted them as text.
Observed behavior: The SUM function returns zero or an incorrect total because Excel cannot mathematically calculate time entries stored as text.

Before you start

Verify if your cells are stored as text by changing their format to 'Number'. If the cell display remains unchanged (e.g., still showing 05:00:00 instead of a decimal), it is stored as text.

Solution 1Recommended

Convert Text to Time Values Using Text to Columns

Use the Text to Columns wizard to quickly force Excel to re-evaluate a whole column of text values into valid time serial numbers.

This is the most efficient method when dealing with large datasets or entire columns of pasted time values that Excel fails to recognize.

Select the data

Highlight the column or range of cells containing the elapsed times stored as text.

Open Text to Columns

Navigate to the 'Data' tab on the top ribbon and click on 'Text to Columns'.

Complete the Wizard

You do not need to change any settings in the wizard. Simply click 'Finish' to apply the conversion.

Apply the SUM function

Use the =SUM() formula on your newly converted time values.

Format for cumulative time

Right-click the result cell, select 'Format Cells', go to 'Custom', and enter [h]:mm:ss to ensure elapsed times over 24 hours display correctly.

Convert Text to Time Values Using Text to Columns

Formatting Tip: Using brackets around the hour [h] tells Excel to display cumulative hours rather than resetting to zero after 24 hours.

Solution 2

Manually Convert Individual Cells

For a small number of cells, you can manually trigger Excel to recognize the text as time by re-entering the cell data.

Advanced Spreadsheet Solution

Calculate Elapsed Times Seamlessly with WPS Spreadsheet

WPS Spreadsheet provides powerful data conversion tools that make it incredibly easy to fix text-formatted numbers and perform complex time calculations. It is highly compatible with traditional spreadsheet software.

1. Open your dataset: Launch WPS Spreadsheet and open the document containing your text-formatted elapsed times.
2. Convert the text: Highlight the time data, navigate to the 'Data' tab, click 'Text to Columns', and immediately click 'Finish'.
3. Sum the converted data: Select an empty cell below your data and enter the formula =SUM(range).
4. Format the total: Press Ctrl+1 to open the Format Cells dialog, select 'Custom', and input [h]:mm:ss to display the correct total elapsed time.

Fully compatible with Microsoft Excel time formatting and calculation functions.Intuitive Text to Columns wizard instantly converts text data to calculable time values.Built-in custom formatting seamlessly supports cumulative time structures like [h]:mm:ss.Lightweight, feature-rich, and completely free alternative for data processing.

Frequently Asked Questions

Why does my total time reset after 24 hours?

By default, spreadsheet software uses a clock-time format, meaning it resets to 0:00 after 24 hours. To display cumulative elapsed time, you must change the cell's custom format to [h]:mm:ss, which uses brackets to prevent the hour count from resetting.

Can I use a formula to convert text to time instead of Text to Columns?

Yes. You can use the =VALUE() or =TIMEVALUE() function in an adjacent column to convert text-based time entries into numeric time serial numbers. You can then sum these new formula results.

How do I add elapsed times that include days?

If your time data includes days, you can format your result cell using the custom format d "days" hh:mm:ss. Make sure the underlying data accurately reflects total time serial numbers (where 1 equals 24 hours).

Excel & Data Problems