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

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.

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

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