DATEVALUE Function returns #VALUE! Error

Copper Contributor

I'v tried setting cell format to Text, any thoughts?


9 Replies


From the help article.


Date_text    Required. Text that represents a date in an Excel date format, or a reference to a cell that contains text that represents a date in an Excel date format. For example, "1/30/2008" or "30-Jan-2008" are text strings within quotation marks that represent dates.

Using the default date system in Microsoft Excel for Windows, the date_text argument must represent a date between January 1, 1900 and December 31, 9999. The DATEVALUE function returns the #VALUE! error value if the value of the date_text argument falls outside of this range.




What are you using ar argument for the DATEVALUE function?

And what is your system date format? M/D/Y as in the USA, D/M/Y as in many European countries, or Y/M/D as in the ISO date format?

I an using a cell as the argument. The Cell is date formatted MM/DD/YY


If the cell used as argument already contains a "real" date, there is no point in using DATEVALUE.

DATEVALUE expects a text string that looks like a date as argument.

If A1 contains the date 10/09/22, =DATEVALUE(A1) will return #VALUE!

If A1 contains a text value that looks like 10/09/22, =DATEVALUE(A1) will return the date 10/09/22.

OK, yes, but I wish to create the serial number form of the date for sorting.
I am going to create a an identifier using several values by concact function. The date serial number will be the first value for sorting


Excel stores dates as numbers, so you can sort them directly, without converting them to numbers.

For example:

10/09/2022 is stored as 44843

10/10/2022 is stored as 44844

10/11/2022 is stored as 44845


You can check this by setting the number format of a range with dates temporarily to General.

(Don't forget to restore the date format afterwards)

OK Thank You..

@Jonathan_2112  - this is utterly stupid but the only way to fix this (for me, in canada) is to change regional language and date settings (on the PC) to English (USA).