+ Reply to Thread
Results 1 to 13 of 13

How to sort data chronologically

  1. #1
    Registered User
    Join Date
    03-04-2009
    Location
    New York, U.S.A.
    MS-Off Ver
    Excel 2003
    Posts
    6

    How to sort data chronologically

    How do I sort chronologically by date?
    Last edited by y789gh; 03-06-2009 at 10:28 AM.

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How to sort data chronologically

    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 the icon 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!)

  3. #3
    Registered User
    Join Date
    03-04-2009
    Location
    New York, U.S.A.
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: How to sort data chronologically

    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.

  4. #4
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: How to sort data chronologically

    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

  5. #5
    Registered User
    Join Date
    03-04-2009
    Location
    New York, U.S.A.
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: How to sort data chronologically

    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.

  6. #6
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: How to sort data chronologically

    The actual workbook would be useful, so we could see the format and content of the cell.

  7. #7
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How to sort data chronologically

    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.

  8. #8
    Registered User
    Join Date
    03-04-2009
    Location
    New York, U.S.A.
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: How to sort data chronologically

    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."
    Attached Files Attached Files

  9. #9
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How to sort data chronologically

    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.

  10. #10
    Registered User
    Join Date
    03-04-2009
    Location
    New York, U.S.A.
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: How to sort data chronologically

    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!
    Attached Files Attached Files

  11. #11
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How to sort data chronologically

    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.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    03-04-2009
    Location
    New York, U.S.A.
    MS-Off Ver
    Excel 2003
    Posts
    6

    [SOLVED!!!] Re: How to sort data chronologically

    I'm not sure how you did it, perhaps it's the natural talent of a rocket scientist but THANK YOU SO MUCH!!!

  13. #13
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How to sort data chronologically

    [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]

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1