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,

Thanks for sharing your experience with us. To better understand the situation, please help to provide the following information:

1.Your operation system. Windows or Mac?

2.Based on your operation system, please follow the steps in this article to capture a screenshot of Product Information or the prompt window.

(To protect your privacy, please mask your email address, Product ID and Device ID in the screenshot before sharing. )

3.Regarding "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 cannot be changed."

Please provide some related screenshots of the changes.

4.What did you do before the issue happens? Update Office or operation system?

5.Does the issue happen to a specific CSV file or all CSV files?

Note: to protect your privacy, please mask any personal or private information in the screenshots before sharing them.

Regards,

Tina

Answer 3

Hi Steffen,

Feel free to share the requested information when you have time.

Regards,

Tina

Answer 4

Hello Tina,

the client is running windows 7 professional.

This is the screenshot of the product information:

This is the raw data(cvs), the result before(xlsx) and the result after(xlsx) the issue appeared:

This is happening on this client with every csv file.

As far as I know no software has been installed, there could have been automatic updates from wsus or sccm though.

Kind regards

Steffen

[private message is removed by forum moderator]

Answer 5

Hi Steffen,

Thanks for the detailed information.

According to the screenshots you provided, the date format of the raw data is yyyy/mm/dd

You want the imported date display in dd.mm.yyyy in the new file.

(If there is something I misunderstood, please feel free to clarify it.)

Did you try the following steps to format the imported data in a customize way to display it in dd.mm.yyyy format?

After importing the raw data in a new file via Data>From Text/CSV>choose the csv file>Import>Load

In the new file, right click the data>Format cells>Number>Custom>in the Type box, type dd.mm.yyyy and click OK.

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

Also, you can try to start Excel in safe mode first, then import the row data with the steps mentioned above, check if you can format the date. 

Safe mode can help to check if the issue is related to an Excel add-in or not.

If you still cannot change the imported date's format, please provide the following information:

1.What is the date format after you import the raw data to a new file?

2.Please create a new raw data, check if the issue happens. It can help to check if the issue is related to the specific csv file or not.

3.Please provide your detailed steps about how you create a csv(raw data) file.

Regards,

Tina