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

Need more help?

You can always ask an expert in the Excel Tech Community or get support in the Answers community.

See Also

Correct a #VALUE! error

DAYS function

Calculate the difference between two dates

Overview of formulas in Excel

How to avoid broken formulas

Detect errors in formulas

All Excel functions (alphabetical)

All Excel functions (by category)

A subscription to make the most of your time

Need more help?

Expand your Office skills
Explore training
Got It
Get instant Excel help
Connect to an expert now
Subject to Got It terms and conditions

Was this information helpful?