Forum Discussion
DATEVALUE Function returns #VALUE! Error
I'v tried setting cell format to Text, any thoughts?
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).
#microsoftbugs
- 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.
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?
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.
