Formula Errors

Fix #VALUE! Error When Calculating Date-Time Differences in Excel

Phi Hung Vo Oct 9, 2026 868 views

Question details

The user needs to calculate the time difference between two date-time columns (like Booked Arrival and Actual Arrival) but encounters a #VALUE! error because the data values are formatted as text strings.

Fixing the #VALUE! Error When Calculating Date-Time Differences in Excel

Product: Excel
Device & OS: not provided
Scenario: Calculating the elapsed time or difference between two specific dates and times.
Observed behavior: Excel returns a #VALUE! error when attempting to subtract date-time cells that have been converted to text using the TEXT function.

Before you start

Verify that your date and time columns are recognized as actual serial numbers by Excel, not as text strings. You can check this by temporarily changing their cell format to 'General' to see if they convert to standard numeric values.

Solution 1Recommended

Use Original Date-Time Values Directly

Avoid combining dates and times using the TEXT function, as this converts them to uncalculable strings. Instead, perform arithmetic directly on the raw date-time cells.

In Excel, dates and times are stored as serial numbers. When you use a formula like =TEXT(O2,"m/d/yyyy")&" "&TEXT(P2,"hh:mm:ss"), Excel converts the underlying number into a pure text string. Subtracting text strings results in a #VALUE! error.

By calculating the difference using the original raw cell references, Excel can properly execute the mathematical operation.

Select the destination cell

Click on the cell where you want the time difference to appear (e.g., U2).

Enter the subtraction formula

Type the formula =24*(T2-Q2), assuming T2 is the Actual Arrival and Q2 is the Booked Arrival. Multiplying by 24 converts the fractional day difference into total hours.

Format the result as a number

Right-click the destination cell, select 'Format Cells', choose 'Number' from the Category list, set your desired decimal places, and click OK.

Apply formula to the column

Click and drag the fill handle at the bottom-right corner of the cell to copy the formula down to the remaining rows.

Formatting Matters: If you don't multiply by 24, Excel will return the difference in days. You can also format the direct subtraction =(T2-Q2) using a custom format like [h]:mm:ss to show total elapsed hours and minutes.

Solution 2

Convert Existing Text Strings to Date-Time Values

If your data has already been exported or combined as text strings and you cannot access the raw data, use the VALUE function to force Excel to read them as numbers.

Work Smarter with WPS Spreadsheet

Easily Calculate Date-Time Differences in WPS Spreadsheet

WPS Office provides an intuitive, highly compatible spreadsheet environment where you can perform complex date-time calculations, troubleshoot formula errors seamlessly, and manage large datasets efficiently.

1. Open your workbook in WPS: Launch WPS Spreadsheet and open the document containing your date and time data.
2. Input the calculation formula: Select an empty cell and enter the formula =24*(T2-Q2) to subtract the raw date-time values and convert to hours.
3. Format as a number: Right-click the cell, select 'Format Cells', choose the 'Number' category, and click OK.
4. Drag to fill: Double-click the small square at the bottom right of the active cell to automatically fill the formula down the entire column.

Fully compatible with all Microsoft Excel formulas, including date, time, and text functions.Smart error-checking tools that quickly identify why a #VALUE! error is occurring.Free, lightweight, and user-friendly interface that requires zero learning curve for Excel users.

microsoft office alternative - wps office

Frequently Asked Questions

Why does the TEXT function cause a #VALUE! error in math?

The TEXT function is specifically designed to convert numeric values (like dates and times) into text strings for display purposes. Mathematical operators like subtraction require numbers; when they encounter text strings, Excel cannot compute them and returns a #VALUE! error.

How can I calculate the difference in minutes instead of hours?

To calculate the difference in minutes, multiply the subtraction result by 1440 (the number of minutes in a 24-hour day). For example, use the formula =1440*(T2-Q2) and format the cell as a General or Number format.

How do I know if my date is formatted as text?

By default, text is aligned to the left of a cell, while numbers (including dates and times) are aligned to the right. You can also select the cell and change its format to 'General'. If it changes to a 5-digit number (like 44562), it is a valid date. If it stays looking like a date, it is stored as text.

Can I combine separate Date and Time columns without using TEXT?

Yes. Since dates and times are both serial numbers, you can simply add them together. If Date is in A2 and Time is in B2, enter =A2+B2 in a new cell. This creates a valid, calculable date-time value without using the TEXT function.