[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:

=C5-B5

4 Reasons with Solutions to VALUE Error When Subtracting Time in Excel

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.

Reason 1: Excel Returns VALUE Error While Subtracting If Time Stored as Text

Solution:

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

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.

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.