LinkBack URL
About LinkBacks
How do I sort chronologically by date?
How do I sort chronologically by date?
Last edited by y789gh; 03-06-2009 at 10:28 AM.
Highlight all the data columns, click on DATA > SORT > Sort By and select the column with the dates as your sort criteria.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Thank you for your response. That was my initial take on it prior to seeking the help thru this forum but was unsuccessful.
Now, some cells within the column have the date formatted for M-D-Y while most other cells have it as D-M-Y (which is what I specified). Even after highlighting the entire column to format them correctly they continue to resist the command.
Even still, within a given month, the dates are not in chronological order, as I've set it to Ascend (from oldest date to newest). For example, 20-Sep-2008 appears above 12-Sep-2008 within the column.
Please help.
Hi,
the format of the date as it is shown in the cell does not influence the way Excel sorts it. So if one cell is d-m-yy and another one is m-d-yy, Excel will still sort them chronoloically, based on the underlying time value, which is a number like 39877 for mar-5-09.
If it appears that Excel does not do the sorting right, it may be that the data is not what it seems to be. Make sure that all your dates are formatted in the same way, so you can easily spot any outliers. Then go and check these "wrongly" sorted values to see if they are really dates. Maybe they are text, not numbers formatted as dates, which would impact on the sort order.
hope that gives you some starting point.
if not, post a sample of your data in a workbook
Thanks Teylyn. I can't seem to get it right though I've copied what I've come up with in the Date column so far:
Date
05-Sep-08
07-Nov-07
01-Dec-07
03-Jan-08
10-Jan-08
10-Jan-08
10-Jan-08
10-Jan-08
11-Jan-08
11-Jan-08
11-Jan-08
11-Jan-08
11-Jan-08
23-Apr-08
18-May-08
10-Jun-08
16-Jun-08
23-Jul-08
01-Aug-08
01-Sep-08
10-Sep-08
15-Sep-08
15-Oct-08
15-Nov-08
15-Nov-08
15-Nov-08
30-Nov-08
01-Dec-08
01-Dec-08
01-Dec-08
01-Dec-08
15-Dec-08
15-Dec-08
16-Dec-08
19-Dec-08
31-Dec-08
31-Dec-08
31-Dec-08
31-Dec-08
31-Dec-08
31-Dec-08
31-Dec-08
31-Dec-08
02-Jan-09
02-Jan-09
03-Jan-09
07-Jan-09
15-Jan-09
15-Jan-09
31-Jan-09
31-Jan-09
31-Jan-09
31-Jan-09
15-Feb-09
15-Feb-09
16-Feb-09
21-Feb-09
28-Feb-09
01-Mar-09
04-Mar-09
09-Mar-09
13-Mar-09
15-Mar-09
15-Mar-09
15-Mar-09
15-Mar-09
15-Mar-09
16-Mar-09
27-Mar-09
31-Mar-09
31-Mar-09
31-Mar-09
31-Mar-09
31-Mar-09
31-Mar-09
01-Apr-09
01-Apr-09
30-Apr-09
30-Apr-09
30-Apr-09
01-May-09
15-May-09
30-Jun-09
31-Jan-10
31-Jan-10
N/A
I did what you said and found that the "stubborn" cells not wanting to convert did in fact have text within the cell. After fixing those cells I tried to sort again (from oldest to newest) and what I've pasted is what has happened.
I appreciate the help.
The actual workbook would be useful, so we could see the format and content of the cell.
My guess is SOME of those cells are really text. Try this useful trick to get the whole column into the same raw format.
Highlight all dates
Click on Data > Text to columns > Delimited > Next > Next > Column Date Format: MDY (or DMY, whatever) > Finish
This is your best bet to get them all tweaked the same in one swoop.
Thank you for your help though I believe it gave me the same results. I've finally figured out how to attach the workbook in question so we're "all on the same page."
The sample data all appears to be dates, and it all sorts fine. Can you post again showing data the won't sort properly, and highlight in the data, even a before/after might help. So far it looks fine.
It would be helpful if examples like you gave in post #3 were in the dataset.
You're right! Only I tried it with my actual workbook and couldn't get it to comply. I edited a large version of it out but it's not top secret rocket science data so here it is in its entirety.
Thank you!
Sorry, mate, having no difficulty sorting this data. I even added a check column to verify that each row is dated earlier or equal to the row prior, any mis-sorting will flag itself this way...got all TRUE answers.
Looks fine to me.
I'm not sure how you did it, perhaps it's the natural talent of a rocket scientist but THANK YOU SO MUCH!!!
[perplexed] Ok...glad to try and help.
If that takes care of your need, be sure to EDIT your ORIGINAL post (Go Advanced) and mark the PREFIX box [SOLVED]
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks