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.
Need more help?
You can always ask an expert in the Excel Tech Community or get support in Communities.
See Also
Calculate the difference between two dates
