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

Get FREE Advanced Excel Exercises with Solutions!

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

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

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

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.

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

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

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

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.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Hosne Ara
Hosne Ara

Hosne Ara is a materials and metallurgical engineer who loves exploring Excel and VBA programming. To her, programming is like a time-saving superhero for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B. Sc. in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, she's shifted gears and now works as a content developer. In this role, she... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo