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

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

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


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.

Reason 1: Excel Returns VALUE Error While Subtracting If Time Stored 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.

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

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.

Reason 2: System Date and Time Settings Is Not Similar to Excel Cell Dates

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:


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.

Reason 3: Excel Cell Contains Leading Spaces in Time Values

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.

Reason 3: Excel Cell Contains Leading Spaces in Time Values

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.

Reason 3: Excel Cell Contains Leading Spaces in Time Values

  • 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.

Reason 3: Excel Cell Contains Leading Spaces in Time Values

  • 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.

Reason 4: Excel Returns VALUE Error When Subtracting Time from String

Solution:

To solve the above problem, simply replace strings with time, and consequently here is the time difference.

Reason 4: Excel Returns VALUE Error When Subtracting Time from String

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

Hosne Ara

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo