Forum Discussion
Incorrect date format when using text to columns
I've got an issue when I am importing a text file and using the text to columns function where its not recoginsing the correct date format in one of the columns. The text file is generated from a system that is based in North America so the date follows the mm/dd/yyyy format. As I am in the UK I need it to be dd/mm/yyyy. For some reason it is able to convert to the correct format for those dates where the day is less than or equal to 12 but when it goes over that it doesnt know what to do with it and just leaves it as text. If one of my collegues in North America carries out the same process it converts them all ok, I'm thinking its a regonal thing but not sure where to start checking. I've attached 2 files, one is the raw txt file, the other is after I import it to Excel. Does anyone have a suggestion as to what the issue might be?
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.
9 Replies
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.
Hi Craig
On third step of the wuzard select correct date format for that column (US one in your case)
Hi Sergei - I've tried this method also but it still ends up with the same results. It converst the ones with days less than or equal to 12 and ignores the rest !! Very frustrating.
Craig, could you please attach text file (or part of it) from which you import?
