Forum Discussion
Incorrect date format when using text to columns
Craig, it looks like due to time added to that field. If time is not important for you (i.e. it always 0:00:00) you may open csv file and Text to Columns last column with space separator, remove time only column after that.
See result attached. If the time is important will find somethin else.
Craig Shankland I had a similar issue. The dates were arranged from oldest to latest. Dates with days greater than 12 were formatted as text (mm-dd-yyyy) while those with days less than 12 were fomatted as dates (mm-dd-yyyy). I solved this by selecting "MDY" after checking the Date option in the Text to columns feature. This formatted the entire column as date with the format dd-mm-yyyy (UK format). Looking through the dataset after I solved this was how I got to know the dates were arranged from oldest to latest. I still don't understand why Excel behaves that way though.
Second Image shows the Date_converted column after I changed it to the Long date format.
I would advise you look through your dataset thoroughly before applying this solution.