willie108
asked on
Excel seems to be treating a date and time as text when sorting
I have two files. They are outputs of two different processes. One treats the date as a date when I try to do a sort in Excel (it says "oldest to newest") but the other Excel treats as text (from A to Z). I want it to treat it as a date but I cannot manually reformat or even do it using VBA. I have to tell the person who designed the process what is wrong. Any suggestions on what is happening here.
Both are attached.
treatsDateAsDate.csv
treatsDateAsText.csv
Both are attached.
treatsDateAsDate.csv
treatsDateAsText.csv
ASKER
Thanks. I did try reformatting manually just to see if that makes a difference but it did not. Does this still agree with your conclusion?
What do you mean by "reformatting"? If it is already in Excel and you try formatting the cell, this will not work because the format only works when Excel already knows it's a date. You need to manually change the text file with a text editor like Notepad++ and then open it in Excel.
ASKER
I see. If I want to change it with Notepad++ would that mean changing the order of the month day and year for each row?
Thanks.
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you.
Oh, you can try this: rename the file to have an extension .txt (instead of .csv). Open Excel and press Ctrl+O (open file). Select all files as file type and choose your file. Excel will ask you how columns are separated. Select separator and select the comma. Then it will show you all columns. Select the one with the date and time and there will be a drop down for the order of the date. MDY means Month/Day/Year while DMY means Day/Month/Year. Select DMY for the not working file and open it. It shall than have the values correctly.
Ah, still doesn't work because of the time :-(
ASKER
Thanks for the extra idea.
ASKER
Oh one other thing. If I change it to day/month/year as you suggested originally, will the fact that the time is there cause any interference?
Yes, it does unfortunately. You can split the columns both by comma and by space but this will break all other columns as well.
I don't think there is an easy way to do that.
I don't think there is an easy way to do that.
ASKER
I am not sure I understand. One of the files I sent treated it as a date in spite of the fact that there is a time in there as well. In the problem file, if I change as you said to day/month/year, then won't Excel treat in like a date in spite of the fact that it too has a time in it?
ASKER
Hi. I just tried to change the date part to month/day/year plus time and it seems to work. It treats it as a date.
If you change it as I said, Excel expects to see a column with date only. However your column is date AND time. So it tries to interpret it as date and fails (the time remains), gives up and writes it as plain text. If it was only date, it would work. What you can do is add manually comma between the date and the time in the text file and then open it in Excel as I described but you will have the date and the time in two separate columns.
Nice!
Working: 979931706,8/18/2012 9:08,...
Not working: 6928 5800708,31/10/2009 12:12:55,...
The working one has the date in the format month/day/year. The not working one is in the format day/month/year. Depending on your system settings one will work, the other not. In your case you have the month first, so I assume you use US settings. Just request the second non-working file to be in the correct format (month first) and it should work.