Often while subtracting date or time in Excel, excel returns #VALUE! error. Usually, the #VALUE error appears when the time value provided to the formula is not of the right kind. For example, when dates are stored as text, subtraction of time values does not return the expected result. In this article, I will explain probable reasons for returning VALUE error (#VALUE) when subtracting time in Excel and will suggest solutions for them.
VALUE Error (#VALUE!) When Subtracting Time in Excel: 4 Reasons with Solutions
While preparing the article, I used a simple subtraction formula to get the difference between problematic time data. The formula I have used is:
=C5-B5
Reason 1: Excel Returns VALUE Error (#VALUE!) While Subtracting If Time Stored as Text
Sometimes, when we type dates that do not match Excel’s date format, excel stores those dates as text. Later when we try to subtract these types of dates from each other, excel returns the #VALUE error. To illustrate, if you type August 09 2021 in a cell, Excel will store the date as text.
Solution:
To solve the above problem, type dates in the correct format like 9-Aug-2021 (see screenshot). Now as time values are saved as Date, upon subtracting, the formula will return the expected time difference.
Read More: How to Calculate Difference Between Two Times in Excel
Reason 2: System Date and Time Settings Is Not Similar to Excel Cell Dates
Suppose, you type a date in the Excel worksheet as mm-dd-yyyy. On the other hand, your computer date format is set as dd-mm-yyyy. That means the date you entered in Excel is not in sync with your system Date and Time settings. Besides, excel stores invalid date values as text. For example, you have typed 01-23-2021 as the date and now you try to subtract these date values from another date value, #VALUE! error will appear.
Solution:
Change the date types in Excel according to your system’s Date and time settings. Such as I have changed my dates to the dd-mm-yyyy format which is synced with my PC. As a result, the subtraction formula gives the desired time difference.
Read More: How to Subtract Date and Time in Excel
Reason 3: Excel Cell Contains Leading Spaces in Time Values
Let’s assume that you have entered date values along with leading spaces. Unfortunately, subtracting these types of date values will return the desired result. Thus excel will return #VALUE! error.
Solution:
You can remove leading spaces by following two methods.
Method 1:
You can remove leading spaces manually by putting the cursor on the corresponding cell.
Method 2:
We can use the Text to Columns feature of Excel to remove leading spaces; and convert to Date format. Let’s go through the below steps:
- Select the column data that contains leading spaces. Then from Excel Ribbon, go to Data > Text to Columns.
- As a result, the Text to Column dialog will appear. Choose Fixed width from Choose the file type that best describes your data. Press Next > Next.
- Choose Date from the Column data format and press Finish.
- Consequently, selected cells will be converted to 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:
To solve the above problem, simply replace strings with time, and consequently here is the time difference.
Read More: How to Calculate Time Difference in Numbers
Things To Remember about VALUE Error When Subtracting Time in Excel
- Cell formatting is important while working with time data in Excel. So check the Number formats of the cells that contain time or date values.
- Check the format of the resulting column too; especially when you are calculating the hour difference between time values.
Download Practice Workbook
You can download the practice workbook that we have used to prepare this article.
Conclusion
In the above article, I have tried to discuss several suggestions to solve #VALUE! error when subtracting time in Excel elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.
