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,

Are you still experiencing the issue? Let us know if you need further help.

Regards,

Tina

Answer 3

Hello Tina,

sorry I was out of the office for a few days.

I am still experiencing this issue.

When trying to format the cells into a custom format, nothing changes, so the result is still the same.

Starting Excel in safe mode did not help.

Using a new csv file does not help.

The file is beeing created by an automated process on an external system, what exactly happens there I can not say, but the admin told me the process has not been changed for over a year.

Kind regards

Steffen

Answer 4

Hi Steffen,

For further investigation, please help to confirm the following information:

1.After you import the CSV file into Excel, did you change the date format via the steps as below? (select the date and right click it first)

2.In your original post, you mentioned "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. "

Did the imported data display the date format as dd.mm.yyyy directly once you finish importing the CSV file to Excel?

If not, please provide your detailed steps before the date format displays in dd.mm.yyyy.

3.Before the issue happens, did you change the date format in the raw data?

4.Please create a new Excel file, type some dates in it and set the date format as yyyy/mm/dd first, then check if you can change the date format to dd.mm.yyyy by customizing the date format via the steps mentioned above.

Please test on your side and let us know the result.

Regards,

Tina

Answer 5

Hello Tina,

1. yes I did exactly as shown in the image.
2. it displayed the correct format directly after the import, i did not have to change anything.
3. i did not change anything in the raw data, that is part of what i want to do in excel.
4. doing this in a blank excel file let's me change the date in every format i want, including dd.mm.yyyy.

Kind regards

Steffen

[private message is removed by forum moderator]