Forum Discussion

michael_d19's avatar
michael_d19
Copper Contributor
Dec 02, 2022
Solved

Sorting Dates using a Customer Sort Field in a Table

I have a list of birthdays and I wanted to sort by date. April 2  October 27  October 7  October 20  September 24  September 23  September 26  April 5  April 15  November 4  September 29  ...
excel
Formulas and Functions
  • dscheikey's avatar
    dscheikey
    Dec 02, 2022

    michael_d19 

    Hi, I would have used an auxiliary column for sorting. With the following formula you get a date (in 2022) to sort by.

    =DATEVALUE(SUBSTITUTE(MID(A2,FIND(" ",A2)+1,2)&"-"&LEFT(A2,FIND(" ",A2))&"-2022",CHAR(160),""))

    It was a bit difficult because your data does not contain a normal space at the end, but a CHAR(160).

    See also the attached sheet.

     

dscheikey's avatar
dscheikey
Bronze Contributor
Dec 02, 2022

michael_d19 

Hi, I would have used an auxiliary column for sorting. With the following formula you get a date (in 2022) to sort by.

=DATEVALUE(SUBSTITUTE(MID(A2,FIND(" ",A2)+1,2)&"-"&LEFT(A2,FIND(" ",A2))&"-2022",CHAR(160),""))

It was a bit difficult because your data does not contain a normal space at the end, but a CHAR(160).

See also the attached sheet.

 

  • michael_d19's avatar
    michael_d19
    Copper Contributor
    Dec 07, 2022

    The crazy thing is I pasted it into a Google Sheet, added the filter, clicked sort A-Z and presto sorted in order as I need it then pasted it back. This is where Microsoft really drops the ball when it comes to users. I could have written a macro too and used a sort algorithm, but I shouldn't have to.

Resources