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

[Cenote]

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]

@Cenote 

Those error appear if the date is a number value.

[#3]

@Cenote 

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.