Function Problems

How to Calculate Time Differences from Text Strings in Excel

Ayan Masood Sep 27, 2026 869 views

Question details

The user needs a formula to calculate the duration or hours between two time values that are stored together as a single text string (e.g., "5am - 2pm").

How to Calculate Time Differences from Text in Excel

Product: Excel
Device & OS: not provided
Scenario: Calculating working hours or durations across multiple rows where the start and end times have been manually typed as a continuous text string.
Observed behavior: Because the time values are stored as a single text string separated by a hyphen, standard mathematical subtraction cannot be applied without first parsing and converting the text into valid time serial numbers.

Before you start

Ensure your text strings follow a consistent format, particularly the delimiter separating the two times (such as a hyphen with spaces " - "). Inconsistent spacing may require you to adjust the formula.

Solution 1Recommended

Use the LET and TIMEVALUE Formula to Extract and Subtract Times

This is the most efficient method for modern spreadsheet software. It parses the start and end times directly from the text string, converts them to time values, and calculates the difference.

This approach leverages modern text functions (TEXTAFTER and TEXTBEFORE) alongside the LET function to streamline the calculation. It also uses the MOD function to seamlessly handle times that cross over midnight.

Locate the target cell

Identify the cell containing your text string. For this example, assume your time range (e.g., "5am - 2pm") is in cell D2.

Enter the formula

Select an empty cell where you want the result, and enter the following formula: =LET(v,SUBSTITUTE(SUBSTITUTE(D2,"am"," am"),"pm"," pm"),MOD(TIMEVALUE(TEXTAFTER(v," - "))-TIMEVALUE(TEXTBEFORE(v," - ")),1))

Format the result as Time

Right-click the cell containing your new formula, select "Format Cells", navigate to the "Number" tab, select "Time", and choose your preferred duration format (like 13:30 or h:mm).

Apply across multiple rows

Click the small square at the bottom-right corner of the selected cell and drag it down (e.g., to D50) to apply the formula to all your rows.

Use the LET and TIMEVALUE Formula to Extract and Subtract Times

Proper Formatting: Formatting the result as 'Time' is crucial; otherwise, Excel will display the result as a decimal fraction representing a portion of a 24-hour day.

Solution 2

Split Text Using the Text to Columns Feature

If you are using an older version of Excel that doesn't support the TEXTAFTER or LET functions, you can manually split the text into two columns and subtract them.

Efficient Data Processing

Calculate Time Differences Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced text parsing and time functions like LET, SUBSTITUTE, and TIMEVALUE. You can seamlessly calculate durations from messy text strings just as you would in Microsoft Excel.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your text-based time data.
2. Select the target cell: Click on the blank cell where you want the calculated hours to appear.
3. Paste the calculation formula: Paste the exact formula provided: =LET(v,SUBSTITUTE(SUBSTITUTE(D2,"am"," am"),"pm"," pm"),MOD(TIMEVALUE(TEXTAFTER(v," - "))-TIMEVALUE(TEXTBEFORE(v," - ")),1))
4. Format and drag: Press Enter, format the cell as Time using the Format Cells menu, and drag the fill handle down to calculate the rest of your data.

Fully compatible with Microsoft Excel formulas and time formatting rules.Advanced text processing functions available for complex data cleaning.Lightweight architecture processes thousands of rows of formulas rapidly.Free to use with a highly familiar, tabbed user interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my time difference formula return a #VALUE! error?

This usually happens if the text formatting is inconsistent, such as missing spaces before 'am/pm', or using a different delimiter than the expected ' - '. Ensure the raw text matches the pattern expected by the formula, or adjust the characters in the SUBSTITUTE function.

How do I calculate time differences crossing midnight?

The MOD function in the formula seamlessly handles times crossing midnight. By wrapping your subtraction inside MOD(End_Time - Start_Time, 1), a shift from 10 PM to 2 AM correctly calculates as 4 hours instead of returning an error or negative number.

What if my software doesn't support the TEXTAFTER or TEXTBEFORE functions?

If you are using a legacy version of your spreadsheet software, you can either use the 'Text to Columns' feature under the Data tab to split the data manually, or use a combination of classic functions like LEFT, RIGHT, FIND, and MID to isolate the time values before calculating.

Excel & Data Problems