Forum Discussion

Copper Contributor

Nov 12, 2022

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

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 c...

excel

Formulas and Functions

office 365

HansVogelaar

MVP

Nov 12, 2022

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.