dd/mm/yyyy to yyyy-mm-dd

[Infine]

Hello,

I need help with a simple DATE converter. I have:

02/01/2018
22/03/2019

I want

2018-01-02
2019-03-22


I use this code:

=TEXT(A1, "yyyy-mm-dd"),

But it does not work, how come? What am I missing? Even formatting (RIGHT CLICK -> FORMAT CELLS) does not work.

 

[JEC]

Try to put a DATEVALUE function around A1 first

 

[Infine]

Do you mean convert all the dates like this: =DATEVALUE(A1) ? If yes, then it does not work. I get "#VALUE!"

 

[Joe4]

It sounds like your dates are entered as Text.
Do you want to return a Text entry, or a valid date entry?

If you want a valid date entry, use this formula:

=DATE(MID(A1,7,4),MID(A1,4,2),LEFT(A1,2))

and then format the cell as yyyy-mm-dd afterwards.

If you want a text entry, then use:

=TEXT(DATE(MID(A1,7,4),MID(A1,4,2),LEFT(A1,2)),"yyyy-mm-dd")