Results 1 to 7 of 7

Sort dates from old to new in different formats

This is a discussion on Sort dates from old to new in different formats within the Excel Questions forums, part of the Question Forums category; Hey I'm sorting these dates from old to new in Excel. All have date formats but the format is different. ...

  1. #1
    Board Regular
    Join Date
    Oct 2009
    Posts
    52

    Default Sort dates from old to new in different formats

    Hey I'm sorting these dates from old to new in Excel.
    All have date formats but the format is different.

    21/11/2017
    22/11/2017
    24/11/2017
    27/03/2018
    19/04/2018
    06/03/2018-07/03/2018
    07/12/2017-08/12/2017
    08/02/2018-09/02/2018
    08/03/2018-09/03/2018

    As you can see Excel is able to sort the dates in this format correct dd/mm/yyyy but in this format it goes wrong dd/mm/yyyy-dd/mm/yyyy (for example 07/12/2017 ) Bot formats are in the same column. Why is this and how can I sort it correctly?

    I'm using office 2010. Same problem in newer versions.

    Thank you.
    Last edited by Billie16; Jul 17th, 2017 at 05:09 AM.

  2. #2
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    6,140

    Default Re: Sort dates from old to new in different formats

    An Excel date is a number.
    21/11/2017 is translated into an Excel date
    08/03/2018-09/03/2018 is not a number it is text.
    So the dates won't get sorted rpoperly.

    Use a Helper column.
    With your dates in column A

    in B1
    =IF(ISNUMBER(A1),A1,LEFT(A1,8)+0)
    If the value in column A is a number then it is an actual date, otherwise it extracts the first 8 characters of the text and by adding 0 turns it into a number.
    So i9n either case you end up with an Excel date that is a number.

    Now sort via column B and A

  3. #3
    Board Regular
    Join Date
    Oct 2009
    Posts
    52

    Default Re: Sort dates from old to new in different formats

    Quote Originally Posted by Special-K99 View Post
    An Excel date is a number.
    21/11/2017 is translated into an Excel date
    08/03/2018-09/03/2018 is not a number it is text.
    So the dates won't get sorted rpoperly.

    Use a Helper column.
    With your dates in column A

    in B1
    =IF(ISNUMBER(A1),A1,LEFT(A1,8)+0)
    If the value in column A is a number then it is an actual date, otherwise it extracts the first 8 characters of the text and by adding 0 turns it into a number.
    So i9n either case you end up with an Excel date that is a number.

    Now sort via column B and A
    Thanks but if I use this formula it says this formula contains an error. (The refference to A1), it's the semicolon or colon that u use.
    Formula is good.

    But If I use this formula it works but =IF(ISNUMBER(G17);G17;LINKS(G17;8)+0) this is te result I get:
    06/03/2020
    07/12/2020
    08/02/2020
    08/03/2020

    That aren't the correct dates.
    It doesn't do the trick. I've seen versions of excel in wich it works.

    06/03/2018-07/03/2018 06/03/2020
    07/12/2017-08/12/2017 07/12/2020
    08/02/2018-09/02/2018 08/02/2020
    08/03/2018-09/03/2018 08/03/2020
    09/01/2018-10/01/2018 09/01/2020
    11/09/2017-12/09/2017 11/09/2020
    Last edited by Billie16; Jul 17th, 2017 at 06:17 AM.

  4. #4
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    6,140

    Default Re: Sort dates from old to new in different formats

    Sorry my mistake try this

    =IF(ISNUMBER(G17);G17;LINKS(G17;10)+0)
    Last edited by Special-K99; Jul 17th, 2017 at 06:37 AM.

  5. #5
    Board Regular
    Join Date
    Oct 2009
    Posts
    52

    Default Re: Sort dates from old to new in different formats

    Quote Originally Posted by Special-K99 View Post
    Sorry my mistake try this

    =IF(ISNUMBER(G17);G17;LINKS(G17;10)+0)
    Thanks but doesn't work because Excel places the 2017 dates at the bottom.
    It makes 07/12/2020 wich shouldn't be last from old to new since it should come before the 2018 dates.

    6/03/2018-07/03/2018 06/03/2020
    08/02/2018-09/02/2018 08/02/2020
    08/03/2018-09/03/2018 08/03/2020
    09/01/2018-10/01/2018 09/01/2020
    11/09/2017-12/09/2017 11/09/2020

    07/12/2017-08/12/2017 07/12/2020
    Last edited by Billie16; Jul 17th, 2017 at 07:20 AM.

  6. #6
    Board Regular
    Join Date
    Oct 2009
    Posts
    52

    Default Re: Sort dates from old to new in different formats

    BTW It does look like Excel uses and knows this format is a dateformat but with an *
    when I look al the number value it says Date but like this *14/03/2001 (for cellvalue 11/09/2017-12/09/2017).
    It doesn't say number, standard or text.

  7. #7
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    6,140

    Default Re: Sort dates from old to new in different formats

    No. This works perfectly.

    If you REPLACE the formula I originally gave you with the more recent one you shouldn't get any 2020 year dates.

    I dont know what language or what country or what version of Excel you are using.
    I am using the English version of Excel 2017 and if I enter this in column B

    =IF(ISNUMBER(A1),A1,LEFT(A1,10)+0)

    it works fine.

    Adjust whatever you need to in that formula for your country.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com