Applies ToExcel for Microsoft 365 Excel for Microsoft 365 for Mac Excel 2024 Excel 2024 for Mac Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2016

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

Need more help?

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

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)

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.