Forum Discussion
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
January 11
June 8
April 28
October 22
February 28
December 3
April 7
March 22
April 21
July 16
January 18
December 12
July 11
I put them in a spreadsheet to do this easily. I changed the format to date. When I saw there was no date function built into the sort, I used the Custom List.
Seeing it did not work I wrote a little formula to leave only the month in another column:
=LEFT(L2,FIND(" ",L2))
The result is less than perfect:
April
April
April
April
April
April
December
December
February
January
January
July
July
June
March
November
October
October
October
October
September
September
September
September
Any ideas why this does not work?
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.
2 Replies
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.
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.
