Forum Discussion
Date format problems
That's since some "dates" are kept as text, some are converted wrongly.
Importing csv file do not rely on automatic conversion. If that's legacy wizard, on third step apply Date to this column with MDY format.
Yes, that is the issue. Some of the text will not reformat to date.
The CSV file is created by the software on the blood test meter which is not accesible by me. Can I control how excel converts it when I open the CSV file?
@Khizar_Hayyat has managed to convert all cell to date format but day and month have been mixed up.
@Khizar_Hayyat How did you convert all to date format? Simple explaination for excel novice please.
It's better not to click on csv file to open it, but import csv into the blank Excel file. How to do - it depends on which version of Excel you are.
Assuming one of the latest for Windows Desktop. Here is also two options. You may enable legacy From text connector in options
and use it as
for older Excel that's the only option by default.
After you select the file it'll be 3-steps wizard, practically same as for Data->Text to Columns which Khizar_Hayat used. Here select separation by delimiter, your delimiter, and on 3rd step of the wizard check Date for the column with dates and select MDY format.
Another way is default for the latest Excel option uses Power Query
which is much more powerful and most probably will give you correct result without additional manipulations, but it requires to invest bit more time to start working with it.
- SergeiBaklan your solution is too powerful but it takes a lot of time but i am sure this is the right way to done it
