Formatting date question ....

[sobeitjedi]

I have data in columns G & H which is 13/11/2019 (ie, 13th November 2019)

I want this date to display as 2019/11/13

When I formal cells > custom and enter the format as yyyy/mm/dd, this looks good. I save (as csv) and reopen the file, but it reverts back to it's original formatting? How do I resolve?

 

[Fluff]

If you open the csv file in a text editor, you should see the dates in yyyy/mm/dd format.
The problem comes when you open in Xl it will convert the dates to your standard date format.
You can get round that by importing the file & setting your preferences on page 3 of the Wizard

 

[sobeitjedi]

I don't see the wizard? Power Query Editor loads up??? What is this?

 

[Fluff]

In that case when PQ loads, just select "Load" & you will have the data in the right format, or you will need to use the "Legacy" version.
In File > Options > Data > check "From text (legacy)" > OK. You should then be able to select that from the ribbon.

 

[sobeitjedi]

In that case when PQ loads, just select "Load" & you will have the data in the right format, or you will need to use the "Legacy" version. - Sorry, the date imported in the original format
In File > Options > Data > check "From text (legacy)" > OK. You should then be able to select that from the ribbon. - still didn't open the traditional wizard.

Infuriating.

 

[Fluff]

Did you open the csv in a text editor & check that the dates were in the correct format?

 

[sobeitjedi]

Yes, see : https://photos.app.goo.gl/8c5P2RWMq9CteTqW6

 

[Fluff]

Ok, having set up the legacy import, on the data tab in "Get & transform" section, there should be "Get Data". Select that & then from the dropdown, select "Legacy wizards" then "From text"
That should open the old import wizard.

However if I open a csv using PQ the dates come-in as yyyy/mm/dd.

 

[sobeitjedi]

Still not sure where I should be looking sorry - my screen looks like this : https://photos.app.goo.gl/bJpKTrhDxUuztdYc9

 

[Fluff]

If you click on "Get data" on the Xl ribbon, you should get an on option called "Legacy Wizards", select that followed by "From Text".
That should load the old style import wizard.