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 again,

sry that I am answering so late.

I asked my admins if it is okay to upload a file like this and if so how I can upload it.

They unfortuantely told me that it is against company policy to upload files like this to non-colleagues/non-customers and that there is no secure way for me to do this.

We have however found out that the headers of the columns are also in the english, rather than german as it was before, and that the date is transformed correctly if we open the csv directly and use "text in columns".

My admins also advised me to reinstall office 365 and to deactivate all addins. This has had no effect as far as I can see, besides taking away my quickprint function.

Is there anything else I can do or check to fix this?

Kind regards

Steffen ****

Answer 3

Hello Sky,

thank you, this works somewhat better.

Is it possible to limit the cells that I want to change to Date/Time so that only the cells with dates in them change and not those with text?

Kind regards

Steffen ***

Answer 4

Hi Steffen,

I have tested it yesterday. Sorry, but I haven't found a way to limit the cells which we want to change to the Date/Time format. Therefore I gave you a second workaround in my last post. It should be work, though there are a lot of steps.

Thanks for your understanding.

Sky