Convert Text Containing Hours and Minutes to Total Minutes in Excel
Question details
The user needs to convert exported text strings that contain mixed time units (e.g., '1 hr. 30 min', '7 hr.', or '30 min') into a single numerical value representing total minutes.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Processing exported system data where time durations are formatted inconsistently as text with mixed units (hours only, minutes only, or both).
- Observed behavior
- The time durations are currently text strings and cannot be used directly in mathematical calculations, requiring a formula to extract the numbers and convert hours to minutes.
Ensure that your exported text data uses consistent abbreviations (like 'hr' and 'min') and remove any unnecessary leading or trailing spaces from the data column before applying the conversion formulas.
Use the LET Formula for Flexible Text Parsing
The LET function is highly recommended because it allows you to define variables within the formula to safely check for the presence of 'hr' and 'min', making it robust for mixed and missing formats.
This approach defines positions for the 'hr' and 'min' text strings, extracts the numerical values preceding them, and calculates the total minutes. It safely handles scenarios where cells contain only hours, only minutes, or a combination of both without throwing errors.
Click on a blank cell adjacent to your text data where you want the total minutes to appear (for example, cell D2 if your data is in C2).
Type the following formula into the formula bar: =LET(hpos,IFERROR(SEARCH("hr",C2),0),hours,IF(hpos,IFERROR(--TRIM(LEFT(C2,hpos-1)),0),0),mpos,IFERROR(SEARCH("min",C2),0),minutes,IF(mpos,IFERROR(--TRIM(MID(C2,IF(hpos>0,hpos+3,1),mpos-IF(hpos>0,hpos+3,1))),0),0),hours*60+minutes)
Press Enter to calculate the total minutes. Then, click and hold the fill handle at the bottom right of the cell and drag it down to apply the formula to the rest of the column.
Use IF, ISNUMBER, and SEARCH Functions (Alternative)
If you are using an older version of a spreadsheet program that does not support the LET function, you can use a combination of nested IF and SEARCH functions.
Use WPS Office to Calculate Complex Time Formulas with Ease
WPS Spreadsheet fully supports advanced functions like LET, IF, SEARCH, and MID. You can seamlessly parse messy text strings and convert them to total minutes, offering a smooth and powerful experience identical to Microsoft Excel.
- 1. Download and Install: Download WPS Office for free from the official website and install it on your device.
- 2. Open Your Data: Launch WPS Spreadsheet and open your exported data file containing the mixed time formats.
- 3. Apply the Formula: Paste the LET or IF formula into the adjacent column and press Enter to calculate the total minutes.
- 4. Save Your Work: Save your processed workbook in the standard .xlsx format to retain full formula compatibility.
Frequently Asked Questions
Why does my formula return a #VALUE! error?
This usually happens if the text doesn't contain the exact substrings 'hr' or 'min' expected by the SEARCH function, or if there are unexpected hidden characters. Ensure your data uses consistent abbreviations and spacing.
Can I convert text formatted as '1h 30m' instead of '1 hr. 30 min'?
Yes, you simply need to modify the search terms within the formula. Replace 'hr' with 'h' and 'min' with 'm' in all instances of the SEARCH and FIND functions in the formula.
Is there a way to separate hours and minutes without complex formulas?
Yes. You can use the 'Text to Columns' feature under the Data tab. Use space as a delimiter to split the text and numbers into separate columns, then use a simple math formula to multiply the hours column by 60 and add the minutes column.
Does the LET function work in all spreadsheet versions?
The LET function is a modern function available in Microsoft 365, Excel 2021, and recent versions of WPS Office. If you are using an older version, you should use the alternative solution relying on IF and SEARCH.
