DATEVALUE() returns #VALUE despite text format. DATE() returns #REF despite number format

Question

I am trying to convert a string date (ie. "19/07/2021") to date format. I have tried the following steps:

1. DATEVALUE returns a #VALUE error, although I have used ISTEXT to verify that the target cell is in fact text format
2. I have split the text into separate Year (2021), Month (07), Day (19) columns (19), and used ISNUMBER to verify that each column is in fact number format. Then I tried DATE(Year,Month,Day), but this returns a #REF error.

Answer 1

Hi,

This is because your system's date format is set to MM/DD/YYYY. Therefore 19/7/2021 is not recognised as a Date. You should use Data > Text to columns > Next > Next > Date > MDY > Finish to convert the text date into a proper date.

Hope this helps.

Answer 2

This solved it. Thanks so much, such a simple fix!

Answer 3

Have you perhaps created a defined name called Date?

Answer 4

Thanks for the response. This is exactly what I had tried already, and described in point number 2 in my post. Strangely the Date function returns a #REF error.

Answer 5

Let's say you have 19/07/2021 in cell A2.

In another cell, for example B2:

=DATE(RIGHT(A2, 4), MID(A2, 4, 2), LEFT(A2, 4))

Format the cell with the formula as a date.

Remark: if you use comma as decimal separator, use semicolons ; instead of commas between the arguments of the functions.