[Fixed!] VALUE Error (#VALUE!) When Subtracting Time in Excel

We used a simple subtraction formula to get the difference between time data. The formula we have used is:

Reason 1 – Excel Returns the VALUE Error (#VALUE!) While Subtracting If Time is Stored as Text

When we type dates that do not match Excel’s date format, Excel stores those dates as text. When we try to subtract these types of dates from each other, excel returns the #VALUE error. If you type August 09 2021 in a cell, Excel will store the date as text.

Solution:

Type the dates in the correct format like 9-Aug-2021 (see screenshot).

Read More: How to Calculate Difference Between Two Times in Excel

Reason 2 – System Date and Time Settings Don’t Match Excel Cell Dates

We put a date in the Excel worksheet as mm-dd-yyyy. The PC date format is set as dd-mm-yyyy. The entered date in Excel is not in sync with the system Date and Time settings, which can cause Excel to store the value as text. We have put 01-23-2021 as the date, which doesn’t get registered as a date (since there’s no 23rd month in a year), so we can’t use it as a value.

Solution:

Change the date types in Excel according to your system’s Date and time settings. We have changed the dates to the dd-mm-yyyy format.

Read More: How to Subtract Date and Time in Excel

Reason 3 – The Cell Contains Leading Spaces in Time Values

Entering a leading space before the date saves the value as a text. Trying to perform mathematical operations with those cells will return the #VALUE! error.

Solution:

Remove leading spaces.

Method 1:

• Go to each cell and remove the space from the value manually.

Method 2:

• Select the column data that contains leading spaces.
• From the Excel Ribbon, go to Data and choose Text to Columns.

• The Text to Column dialog will appear.
• Choose Fixed width from Choose the file type that best describes your data.
• Press Next and Next.

• Choose Date from the Column data format and press Finish.

• The selected cells will be converted to the Date format and you will get the expected time difference from the subtraction formula.

Read More: How to Subtract Time and Convert to Number in Excel

Reason 4 – VALUE Error (#VALUE!) When Subtracting Time from String in Excel

If you subtract a string from time, Excel will return #VALUE! error.

Solution:

Put correct values in the cells you’re using in formulas.

Read More: How to Calculate Time Difference in Numbers

Download the Practice Workbook

Related Articles

• How to Subtract Hours from Time in Excel
• How to Calculate Time Difference in Minutes in Excel
  
• How to Subtract and Display Negative Time in Excel
• How to Subtract Military Time in Excel
• How to Subtract Minutes from Time in Excel

<< Go Back to Subtract Time |Calculate Time | Date-Time in Excel | Learn Excel