+ Reply to Thread
Results 1 to 9 of 9

Date sorting says sort A to Z

  1. #1
    Registered User
    Join Date
    04-16-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    11

    Date sorting says sort A to Z

    Hi

    I've got a date field that originally was like this 22.05.2020. I tried all sorts to get it to display in 22/05/2020 display. I created another field derived from it using = text(A2, "DD/MM/YYYY" which worked, but it then wouldn't sort largest or smallest, it was still on A to Z.

    I then looked up solutions and found one that says sort using sort and filter > largest to smallest, but it still says A to Z, what is the deal with this why won't it recognise a date when it sees it?

    Very Annoying!

    Thanks

    Andrew

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,454

    Re: Date sorting says sort A to Z

    Why not CTRL + H to change the date.

    22.05.2020 change . (dot) with / (slash)

    After that you can sorting the data.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,466

    Re: Date sorting says sort A to Z

    It doesn't actually work. It doesn't convert it to a date. So you ARE still sorting TEXT.
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Registered User
    Join Date
    04-16-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Date sorting says sort A to Z

    That's my point, sorry I never made it clearer. It just sticks on text type, even though I've applied date format to the whole column. The file is a download, maybe it's some format stuck in it, although why wouldn't Excel recognise it, especially after changing the data to a date format, then to a date type?

  5. #5
    Registered User
    Join Date
    04-16-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Date sorting says sort A to Z

    I already done the same thing effectively, using substitute(A2, ".","/")

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,466

    Re: Date sorting says sort A to Z

    Try selecting the column then using Text to Columns. Choose DMY as the format for the column.

  7. #7
    Registered User
    Join Date
    04-16-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Date sorting says sort A to Z

    Thanks that done the trick!

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,466

    Re: Date sorting says sort A to Z

    You're welcome.



    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

  9. #9
    Registered User
    Join Date
    04-15-2013
    Location
    Singapore, Melbourne
    MS-Off Ver
    Office 365
    Posts
    45

    Re: Date sorting says sort A to Z

    try this
    =--substitute(A2,".","/")

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Sort Code not Sorting
    By JayBird in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-20-2019, 11:58 AM
  2. [SOLVED] Sorting with vba, custom sort?
    By LIL2606 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-15-2019, 05:39 PM
  3. [SOLVED] Power Query Sorting by Date not sorting correctly
    By kersplash in forum Excel General
    Replies: 5
    Last Post: 08-20-2018, 02:59 AM
  4. Replies: 11
    Last Post: 02-01-2016, 03:43 PM
  5. [SOLVED] When sorting other columns don't sort- how to fix?
    By kevwilsondj in forum Excel General
    Replies: 3
    Last Post: 10-27-2015, 04:09 PM
  6. [SOLVED] How to sort the data by item code first, then sort them by date?
    By Zecond in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-04-2013, 01:55 AM
  7. Sorting by Date--Need help when sorting by 2-digit year
    By Farris_TN in forum Excel General
    Replies: 3
    Last Post: 06-20-2013, 11:20 AM

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