Excel CSV date type automatic conversion

Question

Hi to all, 

I've come accross a rather irritating problem which I'm unable to resolve. 

I have a CSV files with 2 date values stored as text - format yyyy-mm-dd. This is the required format on the target system for an interface that we run via 3 csv files. 

I have to open this CSV file to manually update the contents periodically. The source date fields are correctly formatted when doing the copy-paste-values routine. 

Whenever I open this file in Excel - don't judge it's necessary - it converts these dates into my regional format, and then saves the csv with the said dates in the incorrect format??

How do I stop this from happening? 

I've tried adding the apostrophe in the PowerQuery transformations that I perform - but this doesn't help. 

Any ideas/comments/suggestions welcome. 

Thanks!

Answer 1

Hi MarthinusVZ,

Excel set the format of date based on your system setting, one way to make Excel show the different date format like this: yyyy-mm-dd, you could change your system setting:

1,In the search box on the taskbar, type control panel, and then select Control Panel.

2,Under Clock, Language and Region, click Change date, time, or number formats

3,Click Regional and Language Options.

4,In the Region dialog box, click Additional settings.

5,Click the Date tab.

6,Set the format to meet your needs.

7,Click OK.

Then when you open the CSV file with date information, you will see a Text import Wizard to let your choose the date format.

Then you shall have the date in the format you want:

If you do not like to change system setting, you could open the CSV file with Excel and change the format in Excel,

Select the date figure > Number Format > More Number Formats > select the format > OK

If you still save the file as CSV, this process needs to be done every time you open the file.

Regards,

Alex

Answer 2

Hi MarthinusVZ,

Haven't heard from you for a couple days, do you still need support on this? if you do, just post back.

Regard,

Alex

Answer 3

Hi Alex, 

After much deliberation and persuasion I managed to get my machine off of the domain policy enforcements. 

I have updated my local regional settings to the required format and that seems to have solved the issue. 

Thanks again for your guidance. 

Regards, 

Marthinus

Answer 4

Hi MarthinusVZ,

Thanks for sharing the result and all the effort you took. Glad to know the problem solved.

If you have any other questions about Microsoft product, welcome to post in our forum anytime.

Regard,

Alex