Hosne Ara
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.
Download Practice Workbook
You can download the practice workbook that we have used to prepare this article.
4 Reasons with Solutions to VALUE Error (#VALUE!) When Subtracting Time in Excel
While preparing the article, I have 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 Subtract/Minus Days from Today’s Date in Excel (4 Simple Ways)
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 systems Date & 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 Add/Subtract Years to a Date in Excel
Similar Readings
- Add Weeks to A Date in Excel [4 Fast & Simple Methods with Template]
- Excel formula to Calculate Age on a Specific Date
- Calculate Number of Days Between Two Dates in Excel (8 Quick Tricks)
- 3 Suitable Excel Formula to Count Days from Date
- Calculate Number of Days between Two Dates with VBA 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; 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 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.
Related Content: How to Count Days from Date to Today Automatically Using Excel Formula
Reason 4: VALUE Error (#VALUE!) When Subtracting Time from String in Excel
If you subtract 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.
Related Content: Number of Months Between Two Dates in Excel: 5 Easy Ways to Find
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.
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.
Related Articles
- How to Add Dates in Excel Automatically (2 Simple Steps)
- Excel Add Months to Date (4 Easiest Examples)
- How to Count Date Occurrences in Excel
- Excel Formula to Find Date or Days in Next Month (6 Quick Ways)
- How to Calculate Average Tenure of Employees in Excel
- How to Subtract 30 Minutes from a Time in Excel (8 Easy Ways)
