Forum Discussion
Imported dates not converting to correct format
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?
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.
8 Replies
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.
Riny_van_Eekelen I am trying on query but I don't know how to fix. I am just newbie. Can help me with my case below?
jmustany That's difficult based on a picture alone. I suspect that the dates are in fact texts and that you need to transform to real dates using a 'Locale'.
- Thankyouthankyouthankyou! This little tip just saved me all kinds of hassle! Seemed like there should have been more letters, but it works.
- Works beautifully, thank you!!!
