Convert Text Such as Days, Hours, Minutes, and Seconds to Excel Time in WPS Office
Question details
The user needs to convert inconsistent text durations such as "2 Days 4 Hours 15 Minutes 30 Seconds" into a real Excel duration that can be calculated and formatted in WPS Spreadsheets.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A worksheet contains duration values stored as text, and the user needs to total, average, or format them as actual time values.
- Observed behavior
- The text duration is not recognized as a time value, so time arithmetic and custom duration formatting do not work.
Make sure your duration text follows a consistent number-unit pattern such as "2 Days 4 Hours 15 Minutes 30 Seconds". If your unit words are abbreviated or mixed, adjust the formula search terms to match your exact text.
Convert text durations to time with a formula in WPS Spreadsheets
Use this formula when you need a quick, macro-free conversion and the text uses consistent number-unit pairs such as "2 Days 4 Hours 15 Minutes 30 Seconds".
The formula uses SEARCH and MID to locate each unit word, extracts the number immediately before it, and divides hours, minutes, and seconds by their equivalents in days. Missing units are treated as zero by IFERROR.
Click the cell where you want the Excel time value to appear, for example B2, with the original text duration in A2.
In the formula bar, enter: =IFERROR(VALUE(TRIM(MID(A2,MAX(1,SEARCH("Days",A2)-3),2))),0)+IFERROR(VALUE(TRIM(MID(A2,MAX(1,SEARCH("Hours",A2)-3),2))),0)/24+IFERROR(VALUE(TRIM(MID(A2,MAX(1,SEARCH("Minutes",A2)-3),2))),0)/1440+IFERROR(VALUE(TRIM(MID(A2,MAX(1,SEARCH("Seconds",A2)-3),2))),0)/86400
Press Enter, then select the result cell, open Format Cells, choose Custom, and enter [hh]:mm:ss so total hours display instead of a clock time.
Drag the fill handle down to apply the formula to other text duration cells, then adjust the cell references if your data starts in a different column.
Create a reusable VBA function for converting text durations
Use this approach when you have many worksheets to process or want to reuse a single custom function without rebuilding formulas.
Convert Text Durations into Excel Time in WPS Office Spreadsheets
You can complete the conversion entirely in WPS Office Spreadsheets using a formula or a reusable VBA function, then apply [hh]:mm:ss formatting to get a real duration that calculates correctly.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, open the workbook that contains the text duration values, and click a blank cell where the result should appear.
- 2. Apply the conversion formula or VBA function: Use the formula method or enter =Text2Time(A2) after creating the VBA function, depending on your preferred approach.
- 3. Format the result as a duration: Select the result cell, open Format Cells, choose Custom, and enter [hh]:mm:ss to show total hours, minutes, and seconds.
- 4. Calculate and save the workbook: Copy the formula down as needed, verify the totals, and save the file in WPS Spreadsheets format or as an Excel-compatible file type.
Frequently Asked Questions
Will the formula work if my text only contains hours and minutes?
Yes. The IFERROR part returns 0 for missing units such as Days or Seconds, so a text like "4 Hours 15 Minutes" still converts correctly.
Why does the result display as a clock time such as 4:15:30 AM?
By default Excel and WPS Spreadsheets show time values as times of day. Apply the custom number format [hh]:mm:ss; the square brackets keep total hours from resetting after 24.
Do I need Microsoft 365 functions like TEXTBEFORE or TEXTSPLIT?
No. The formula in this guide uses SEARCH, MID, VALUE, TRIM, and IFERROR, which are available in WPS Spreadsheets and Excel versions without Microsoft 365 dynamic arrays.
Can I use VBA in WPS Office to convert durations?
Yes, many WPS Spreadsheets editions support VBA macros. If the Developer tab or Visual Basic Editor is unavailable, use the formula solution or check macro security settings.
What if my duration text has words in singular form like Day or Hour?
Adjust the formula search terms from "Days", "Hours", "Minutes", and "Seconds" to "Day", "Hour", "Minute", and "Second" to match the exact unit words in your data.
