+ Reply to Thread
Results 1 to 9 of 9

Unable to sort dates by oldest to newest

  1. #1
    Registered User
    Join Date
    04-13-2013
    Location
    Calcutta
    MS-Off Ver
    Excel 2013
    Posts
    87

    Unable to sort dates by oldest to newest

    Hi

    So here is the situation. I have an excel 2007 spreadsheet that has a column with dates that I need to sort from oldest to newest or vice versa. The dates were having a mixed format of mm/dd/yyyy and mm-dd-yyyy

    Here's what I did so far:

    1. Selected the column and formatted the cells to a standard mm/dd/yyyy
    2. Did Text-to-Column > Finish
    3. Even after step #2, I was still unable to sort them from oldest to newest.
    4. So I went to Text-to-Column > Next > Selected Date > Finish
    5. The column still wouldn't sort date wise. So I repeated the above process #4 and this time, I changed the date format to DMY and back again to MDY
    6. This time I was getting the sort from Oldest to Newest option but all the dates didn't switch back to the same format. Some remained in mm/dd/yyyy while some changed to dd/mm/yyyy

    The weird thing is that, if after I perform step #2 and copy the column to a new sheet, I would immediately get the sort datewise option right away, but it isn't happening on the existing sheet.

    Please find attached a sample list of dates attached. No action has been performed on this column yet

    Kindly advise where I am going wrong

    Hope I was able to explain my situation properly
    Attached Files Attached Files

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.82 - (24021116)
    Posts
    8,574

    Re: Unable to sort dates by oldest to newest

    the dates are text and formatting is not really going to help here

    all the dates in your sample have // format none have - -

    theres is no way of knowing if

    09/07/16
    is the 7th Sep or the 9th July
    because in excel at the moment its just text and NOT a date with a format wrong.

    excel uses a number from 1900 for dates
    so today for example 25th june 2016 is a number 42546 days from 1900
    tomorrow will be 42547

    and so excel will look at that number and when you say format as date - it know what to do
    at the moment you do NOT have the numbers in excel - so it just treats
    25/06/16 and 06/25/16 the same as AB12CD - just text
    Last edited by etaf; 06-25-2016 at 04:16 AM.
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    04-13-2013
    Location
    Calcutta
    MS-Off Ver
    Excel 2013
    Posts
    87

    Re: Unable to sort dates by oldest to newest

    Thanks etaf for your reply. So how do I sort these dates from oldest to newest or vice versa?

  4. #4
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.82 - (24021116)
    Posts
    8,574

    Re: Unable to sort dates by oldest to newest

    you cant
    as i say you dont know
    09/07/16
    is the 7th Sep or the 9th July

    you can extract the middle 2 characters and if > 12 then you know its DD/MM/YY

    but that will not help for dates that are
    days 1-12
    and
    months 1-12

    which means day and month can change and still give correct result

    01/12/16
    is 1st dec
    or
    12th jan

    03/09/14
    3rd sep or 9th mar
    Last edited by etaf; 06-25-2016 at 05:26 AM.

  5. #5
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,237

    Re: Unable to sort dates by oldest to newest

    Maybe convert it this way
    Attached Files Attached Files
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  6. #6
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.82 - (24021116)
    Posts
    8,574

    Re: Unable to sort dates by oldest to newest

    popipipo
    problem with that is you are assuming US format

    ie
    04/10/1979 - you converted to 10th April
    BUT from the OP post it may also be
    4th Oct

  7. #7
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,237

    Re: Unable to sort dates by oldest to newest

    04/10/1979 - you converted to 10th April
    BUT from the OP post it may also be
    4th Oct
    mixed format of mm/dd/yyyy and mm-dd-yyyy
    The first 2 numbers are always the month


    The format of the external source should be the same for all data.
    Copy this data as text in excel.

    Then use this formula from the raw data that you get from another source

  8. #8
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.82 - (24021116)
    Posts
    8,574

    Re: Unable to sort dates by oldest to newest

    missed that
    well spotted
    i picked up on
    Some remained in mm/dd/yyyy while some changed to dd/mm/yyyy
    Last edited by etaf; 06-25-2016 at 06:59 AM.

  9. #9
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,237

    Re: Unable to sort dates by oldest to newest

    Some remained in mm/dd/yyyy while some changed to dd/mm/yyyy
    That is an action that excel automatically does when you enter the cel with eg 04/10/1979

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Dates seem formatted correctly, but will not sort newest to oldest.
    By MelindaCapri in forum Excel General
    Replies: 6
    Last Post: 02-11-2015, 02:34 PM
  2. How to change this code to sort dates from the oldest to the newest
    By bazofio in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-29-2014, 05:24 PM
  3. [SOLVED] Sort dates in column from oldest to newest...
    By ILoveYouExcel in forum Excel General
    Replies: 2
    Last Post: 06-11-2014, 02:11 PM
  4. Arranging Pivot table data from Newest to oldest to Newest does not appear
    By paradise2sr in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-08-2014, 06:53 AM
  5. [SOLVED] VBA to sort columns left to right - oldest to newest
    By kenadams378 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-17-2013, 10:36 AM
  6. [SOLVED] vba to sort columns according to dates in header from oldest to newest.
    By rocksan in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-25-2012, 12:57 AM
  7. Top ten oldest and top ten newest
    By jhiltabidel in forum Excel General
    Replies: 11
    Last Post: 07-08-2011, 04:59 PM

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