How to correct a #VALUE! error in the DAYS function

This topic lists the commonly found causes of the #VALUE! error when working with DAYS.

Problem: The arguments do not refer to a valid date

If your arguments contain dates in the form of text, such as June 22 2000, Excel cannot interpret the value as a valid date and throws out the #VALUE! error.

Note: A date in a text format such as 21-Oct-2006 is accepted by the function, and will not result in a #VALUE! error.

Solution: Change the value to a valid date, for example, 06/22/2000 and test the formula again.

Problem: Your system date and time settings are not in sync with the dates in the cells.

If your system follows the mm/dd/yy date setting and your formula follows a different setting, for example, dd/mm/yyyy, you will receive the #VALUE! error.

Solution: Check your system’s date and time settings to make sure that they match the format of the dates referred to in the formula. If necessary, update the formula to the correct format.

The following example shows different variations of DAYS, where A2 and A7 are invalid dates.

Output of various DAYS functions

Do you have a specific function question?

Post a question in the Excel community forum

Help us improve Excel

Do you have suggestions about how we can improve the next version of Excel? If so, please check out the topics at Excel User Voice.

See Also

Correct a #VALUE! error

DAYS function

Calculate the difference between two dates

Overview of formulas in Excel

How to avoid broken formulas

Use error checking to detect errors in formulas

All Excel functions (alphabetical)

All Excel functions (by category)

Connect with an expert
Contact us
Expand your skills
Explore training

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.