Excel import from CSV-file dateformat defaults to US and can not be changed

Question

Hello,

I have been importing CSV files into Excel using the "From Text" function for some time now. The CSV file is coded in UTF-8 and has been for a long time.

It has always displayed dates using the correct dateformat, in my case german (dd.mm.yyyy), but starting this monday it displays as US dateformat and can not be changed.

I tryed formating the cell to date and userdefined, I checked my Systems localisation and region settings.

Both don't seem to be fixing this.

Is there something else I can check?

Thanks

Steffen

Answer 1

Hi Steffen,

I can reproduce the behavior you mentioned using your CSV. After I open the Power Query Editor, I found data type of the time is Text. It should be the cause of the issue.  If I change the data type to data/time, the time format will be what you wanted , like dd.mm.yyyy. But the date above the time in column1 may not be correct.

Or, you may try the following workaround to see if it helps:

1.Open the CSV file -> save it as xlsx file, such as test.xlsx

2.Open test.xlsx -> under data tab, select get data from workbook -> select test.xlsx -> load the power query again -> at this time, you should be able to customize the data type.

Regards,

Sky

Answer 2

Hi Steffen,

Could you help to confirm and provide the following information?

1.Based on your description, the issue happens to every csv file you use to import to Excel. To test on our side, please provide a sample csv file in private message. You can check the PM via this link: https://answers.microsoft.com/en-us/privatemessage/inbox

2.You mentioned "I checked my Systems localisation and region settings.", to test on our side, could you clarify your settings?

Did you set the followings? Control Panel>All Control Panel Items>Region and Modern Language>Formats>set German(Germany) for format, set dd.MM.yyyy for Date and time formats.

If not, please provide your detailed steps.

3.When import the csv file to Excel, did you use the steps as the following screenshots? If not, please clarify your detailed steps.

1)

2)browse and select the csv file, click Import.

3)click Load to import the file directly.

We appreciate your time and patience.

Regards,

Tina

[Updated by Tina Chen MSFT, Feb 2nd, 11:06 AM (UTC)]

Answer 3

Hello Tina,

regarding 1. you should have recieved a pm.

Regarding 2. and 3. I did exactly what you described. The settings are German(Germany) and dateformat is set to TT.MM.JJJJ wich is dd.MM.yyyy.

Kind regards

Steffen

[private message is removed by forum moderator]

Answer 4

Hi Steffen,

We have released a new version 1708(Build 8431.2215) recently. Please upgrade to this version and let us know if it works.

Note: to install updates, check this article.

Regards,

Sky

Answer 5

Hi Steffen,

Do you have any updates?

Regards,

Sky