Formatting date question ....

sobeitjedi

Board Regular
Joined
Mar 13, 2006
Messages
179
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,132
Office Version
365
Platform
Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,132
Office Version
365
Platform
Windows
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

Board Regular
Joined
Mar 13, 2006
Messages
179
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.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,132
Office Version
365
Platform
Windows
Did you open the csv in a text editor & check that the dates were in the correct format?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,132
Office Version
365
Platform
Windows
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,132
Office Version
365
Platform
Windows
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.
 

Forum statistics

Threads
1,077,687
Messages
5,335,662
Members
399,033
Latest member
thefinu

Some videos you may like

This Week's Hot Topics

Top