# [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`

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

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

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

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

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , and premium Excel consultancy services for Excel and business users. Feel free to contact us with your Excel projects.
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

Advanced Excel Exercises with Solutions PDF