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

Occasional Visitor

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.

I am using Office 365

2 Replies


Those error appear if the date is a number value.



Also posted here 

Please mention explicitly if you cross-post in different forums, with a link.


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.