DATEVALUE Function returns #VALUE! Error

[Jonathan_2112]

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

[#2]

@Jonathan_2112 

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.

---

[#3]

@Jonathan_2112 

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?

[#4]

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

[#5]

@Jonathan_2112 

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.

[#6]

OK, yes, but I wish to create the serial number form of the date for sorting.

[#7]

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

[#8]

@Jonathan_2112 

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

etc.

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)

[#9]

OK Thank You..

[#10]

@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