SOLVED

Imported dates not converting to correct format

New Contributor

I'm importing data from SAP to excel in csv file. Data has dates in MM/DD/YYYY format. 

When I take the data to excel and use 'Text to Columns' function, some of the dates are automatically converted to dd.mm.yyyy format and some remain in original MM/DD/YYYY format. Excel does this autoconversion because my system language is Finnish, and accordingly the default date format is dd.mm.yyyy. 

- These which remain as MM/DD/YYYY are not recognized as dates by excel and no conversion is done to them 

- all dates look similar in original csv file 

 

As a result I have column where some fields are dates and some plain text.

Basically date related analysis is not possible with this. 

 

Has anyone come across this type of a problem before and any workaround what I could try out? 

2 Replies
best response confirmed by LKO2022 (New Contributor)
Solution

@LKO2022 When you do Text-to-columns, make sure that you choose the correct setting for the date column is step 3. Select Date and MDY as the source contains dates in the Month, Day, Year format. Now, Excel will import the dates correctly.

 

Alternatively, use Power Query (a.k.a. Get & Transform Data). Much more powerful than text-to-columns.

Works beautifully, thank you!!!