DATEVALUE doesn’t work anymore

[John]

I have a lot of exports in which dates are formatted as “dd.mm.yyyy.” and I’ve previously always used the DATEVALUE function to convert those to dates. Why doesn’t this work anymore?

[administrator@dailyexcel.net]

Hi John,

I also remember using the DATEVALUE function in that way in pre-Excel 2021 versions; there is even an example given in this blog post, and it also does not work for me in the Excel 2021 or Microsoft 365 version.

On the other hand, text strings such as “dd-mm-yyyy” are still accepted by the function!

Br,

[John]

Why would an Excel function change the way it calculates between versions!?!

[administrator@dailyexcel.net]

If it helps, you can use the SUBSTITUE function to clean up your text string before giving it to the DATEVALUE function, something like this:

=DATEVALUE(SUBSTITUTE("dd.mm.yyyy.";".";"";3))

Br,

• This reply was modified 3 days, 1 hour ago by administrator@dailyexcel.net.

[Author]

Posts