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

Hello Sky,

sry for the delay, our updates are managed by the administrators of our parent group Danaher and they take some time to test updates.

However the update has been installed yesterday, unfortunately the problem has not been resolved.

Kind regards

Steffen ****

Answer 3

@Steffen Riek

Check thread Date format in Excel 2016 Power Query that seems very similar to yours. In that other thread the OP found a solution somewhere else (link provided)

Hope this helps

Answer 4

Hello Lz.,

thank you for the quick answer.

I checked the thread and the link given within the thread.

It told me to check the regional setting under "Get Data" -> "Query Options" and to set them to the required location.

I have done so, as it had been set to German(Germany) I have tried setting it to English(United States) and then back again.

Still the issue persists.

Kind regards

Steffen ****

Answer 5

Hey Steffen

Only thing I can offer is to test this here with a non-EN (I'm in FR) OS (running Windows 10 x64 + Excel 2016/365). If you're interested please upload a CSV file on OneDrive (or any other service) and the Query details