folks.

I am cleaning a dataset however in the Date row I get a strange number when I convert it do Date.

I have this:

And I get this:

What I wanted is dd-mm-yyyy.

What I tried:

I went to More Formats and manually separated the date, but it is still not recognised "Date".

I know this must be very basic, but I ave looked everywhere and couldnt find a solution.

CodePudding user response：

the "date" in your D column is a string /plain text you will need to convert it into date with helper column

=ARRAYFORMULA(IFERROR(DATE(LEFT(D2:D; 4); MID(D2:D; 5; 2); RIGHT(D2:D; 2))))