Hello
I have an excel 2007 document with a column of dates in the following format
MM/DD/YYYY
There are about 75K entries in that column and I would like to have the column arranged from the earliest to the latest.
However, when I click on the sort button, I am only getting the options to sort from A-Z and vice versa. I am not getting the option to sort from Smallest to Largest.
Kindly advise
Thanks
Can you post a sample of your data?
Hey JieJenn
Thanks for your reply.
Please find a sample data attached. I am trying to sort the Admitted column from earliest to latest
Many thanks
Try again. I use Excel 2007 and sort choices on dates is Oldest to Newest and Newest to Oldest. I looked at your worksheet and you have the date column formatted as General not as Dates
Last edited by jerrydixon; 06-15-2015 at 02:59 PM.
For some reason, your date field is being treated as "text". Highlight column D, and do a Text To Columns (check Delimited then click Finish) to convert Text to Date, then try to sort again.
Thanks
I tried converting through text to column and some of the cells converted and some didn't.
I noticed that many cells changed their format to DD-MM-YY while the others remained at the previous format of MM/DD/YYYY and hence I am still not getting the option to sort from Oldest to Newest
I have actually taken a part from the datasheet in this new sample, where you can see both the formats and how doing a text to column isn't changing the format for the rest of the cells (D6 onwards)
Please advise what's going wrong here
Thanks
Hum... Are we looking the same column (Column D)? When I highlight column D, and do a quick Text To Columns by Delimited (No condition), it works fine for e.
Well, it doesn't change for me just like that, but when I go to the third step in the Txt to Column wizards and select the date format from the dropdown, it changes all the cells.
Oh. Just highlight column D, and click Finish on Step 1
No luck JieJenn, even the sample I last provided doesn't change this way. I don't know if it's a bug with my excel or anything
However, I think I got what I wanted by changing the date format in the text to column wizard. I think that works for me now
A huge thanks to you all for your prompt support
..I am still wondering why it didn't work for me though..
Excel has difficulties with importing dates
Some it sees dates as text.
12 jan 2015 format MM/DD/YYYY => 1-12-2015
But 1-12-2015 with (standard excel) format DD/MM/YYYY is 1 dec 2015
1-13-2015 is not possible in standard format then excel thinks it is text.
Willem
English is not my native language sorry for errors
Please correct me if I'm completely wrong
I would do this with 2 helper columns...
H2=DATE(RIGHT(D2,4),LEFT(D2,2),MID(D2,4,2))
J2=DATE(RIGHT(G2,4),LEFT(G2,2),MID(G2,4,2))
both copied down.
You can then sort on that. If necessary, you canthen either hide teh helpers, or copy/paste them back over the original data and then delete teh helpers
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks