Excel: Sum Text Durations and Display HH:MM:SS Totals
Question details
The user needs to convert duration text into time values, calculate the combined duration, and display the total in hours, minutes, and seconds.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Duration entries such as 1h 41m 46s and 38s are stored as text in cells A1:A9 and need to be totaled.
- Observed behavior
- Text durations cannot be summed directly, and standard time formatting may wrap totals exceeding 24 hours.
Place each duration text entry in one cell, such as A1:A9, and keep the hour, minute, and second markers consistent. Confirm whether your Excel version supports TEXTBEFORE and TEXTAFTER before using the single-cell array formula.
Convert text durations with TEXTBEFORE and TEXTAFTER
Use this formula in newer Excel versions that support TEXTBEFORE and TEXTAFTER to parse and total mixed duration text.
The formula extracts hours, minutes, and seconds when any of those units are present, treats missing units as zero, and sums the resulting time values. Apply the [h]:mm:ss format so the displayed hours continue beyond 24.
Put the text durations in cells A1:A9, using entries such as 1h 41m 46s or 38s.
Select an empty result cell and enter =SUM(TIME(IFERROR(--TEXTBEFORE(A1:A9,"h"),0),IFERROR(--TEXTBEFORE(IFERROR(TEXTAFTER(A1:A9,"h "),A1:A9),"m"),0),IFERROR(--TEXTBEFORE(IFERROR(TEXTAFTER(A1:A9,"m "),A1:A9),"s"),0))). Press Enter to calculate the total.
Right-click the result cell, choose Format Cells, select Custom, enter [h]:mm:ss in the Type box, and select OK.
Normalize durations into separate columns for older Excel versions
Use this method in Excel 2016 or another version without TEXTBEFORE and TEXTAFTER by separating hours, minutes, and seconds before totaling.
Convert and Total Text Durations in WPS Spreadsheet
WPS Spreadsheet can help convert duration text into numeric time values, total the results, and display elapsed hours correctly with the [h]:mm:ss custom format, including totals longer than 24 hours.
- 1. Enter duration text: Open WPS Spreadsheet and enter values such as 1h 41m 46s and 38s in a single column.
- 2. Parse and sum the values: Select a blank result cell and use a supported TEXTBEFORE/TEXTAFTER formula, or place hours, minutes, and seconds in separate columns and calculate the total with SUMPRODUCT.
- 3. Set the elapsed-time format: Select the result cell, open the Format Cells dialog, choose Custom, and enter [h]:mm:ss.
- 4. Check the total: Verify that the displayed hours continue increasing past 24 instead of restarting at 00.
Frequently Asked Questions
Why does my duration total reset after 24 hours?
The h:mm:ss format displays clock time and wraps after 24 hours. Select the result cell, open Format Cells > Custom, and use [h]:mm:ss to show cumulative hours.
Can Excel sum values such as 1h 41m 46s directly?
No. These entries are text, so they must first be parsed into hours, minutes, and seconds. In supported versions, use TEXTBEFORE and TEXTAFTER; in Excel 2016, separate the units into numeric columns first.
What formula totals text durations in newer Excel versions?
For entries in A1:A9, use =SUM(TIME(IFERROR(--TEXTBEFORE(A1:A9,"h"),0),IFERROR(--TEXTBEFORE(IFERROR(TEXTAFTER(A1:A9,"h "),A1:A9),"m"),0),IFERROR(--TEXTBEFORE(IFERROR(TEXTAFTER(A1:A9,"m "),A1:A9),"s"),0))). Then format the result as [h]:mm:ss.
How do I calculate the total in Excel 2016?
Normalize the text into separate numeric hour, minute, and second columns, such as E7:G7, then enter =SUMPRODUCT({3600,60,1},E7:G7)/86400. Format the result with [h]:mm:ss.
