Convert Text Like "57 Days 05:22:48" into a Time Value in Excel
Question details
The user needs to convert a text string representing a time duration (e.g., "57 days 05:22:48") into a numeric time value.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Preparing data for chart axes or performing time-based calculations.
- Observed behavior
- Excel recognizes the input as a text string instead of a valid numeric time interval, preventing mathematical operations and charting.
Ensure your text string strictly follows the pattern '[number] days [hh:mm:ss]' with consistent spacing before applying the extraction formula.
Use Text Extraction and TIMEVALUE Functions
Combine text extraction functions to separate the days from the time portion, then convert them into a standard numeric time value.
Since Excel does not natively recognize strings like "57 days 05:22:48" as time intervals, you must parse the string mathematically. By extracting the day portion as an integer and converting the remaining time portion into a decimal via the TIMEVALUE function, you get a clean numeric interval.
Click on a blank cell adjacent to the data you wish to convert, for example, cell B2 (assuming your text data is in A2).
Type the formula =LEFT(A2,FIND(" ",A2)-1)+TIMEVALUE(MID(A2,FIND(" ",A2,FIND(" ",A2)+1)+1,8)) and press Enter. This extracts the day number and adds it to the converted time decimal.
Right-click the result cell and select "Format Cells". Go to the "Custom" category. Type [h]:mm:ss to display total hours, minutes, and seconds, or choose "General" to show the value in decimal days.
Click and drag the fill handle at the bottom right corner of the calculated cell downwards to apply the formula to the rest of the data in your column.
Perform Complex Data Conversions in WPS Spreadsheet
WPS Spreadsheet fully supports advanced text extraction and time value functions like LEFT, MID, FIND, and TIMEVALUE, allowing you to convert complex data strings seamlessly for advanced charting.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your text-based time data.
- 2. Apply the parsing formula: Enter the combined LEFT and TIMEVALUE formula in an empty adjacent column to convert the text to numbers.
- 3. Format and Chart: Use the Format Cells dialog to apply [h]:mm:ss custom formatting, then directly select the calculated column to insert your chart.
Frequently Asked Questions
Why does my formula return a #VALUE! error?
A #VALUE! error usually occurs if the text string does not match the exact expected format, such as missing spaces, extra spaces, or an invalid time format within the text. Double-check the spacing in your original data.
How can I display the converted value as just the total number of hours?
After applying the conversion formula, right-click the cell, select Format Cells, navigate to Custom, and enter [h] as the format code. This will display the total accumulated hours, overriding the standard 24-hour rollover.
Can I use Text-to-Columns to solve this instead of a formula?
Yes, you can use the Text to Columns feature under the Data tab using the space character as a delimiter. This will separate the numeric days, the word 'days', and the time into different columns. You can then add the day column and the time column together manually.
