Sign in with Microsoft
Sign in or create an account.
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.

Important: When using Date functions in Excel, always remember that the dates you use in your formula are affected by the Date and Time settings on your system. When Excel finds an incompatibility between the format in the date_text argument and the system’s Date and Time settings, you will see a #VALUE! error. So the first thing you will need to check when you encounter the #VALUE! error with a Date function is to verify if your Date and Time settings are supporting the Date format in the date_text argument.

Here are the most common scenarios where the #VALUE! error occurs:

Problem: The date_text argument has an invalid value

The date_text argument has to be a valid text value, and not a number or a date. For example, 22 June 2000 is a valid value, but the following values are not:

  • 366699

  • 06/22/2000

  • 2000 June 22

  • June 22 2000

Solution: You have to change to the correct value. Right-click on the cell and click Format Cells (or press CTRL+1) and make sure the cell follows the Text format. If the value already contains text, make sure it follows a correct format, for e.g. 22 June 2000.

Problem: The value in the date_text argument is not in sync with the system’s date and time settings

If your system date and time settings follow the mm/dd/yyyy format, then a formula such as =DATEVALUE(“22/6/2000”) will result in a #VALUE! error. But the same formula will display the correct value when the system's date and time is set to dd/mm/yyyy format.

Solution: Make sure that your system’s date and time settings (both Short time and Long time) matches the date format in the date_text argument.

Problem: The date is not between January 1, 1990 and December 31, 9999

Solution: Make sure that the date_text argument represents a date between January 1, 1990 and December 31, 9999.

The following example lists the output of different DATEVALUE functions.

Note: For this example, the Date and Time settings are set to M/d/yyyy and dddd,MMMM d,yyyy for the Short Date and Long Date formats respectively.

Output of various DATEVALUE 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

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

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!