Best way to sort a table by month & day ignoring year

[JenniferMurphy]

I have a table of family members with their dates of birth. I would like to sort it by month and day ignoring the year. Here is a sample table. The only way I could think of was to add a helper column with just the month and day. Is there a better way that does not require the helper column?

Christmas & Birthday Candy.xlsx
	C	D	E
4	Name	DoB	m/dd
5	Joe	1/21/1980	01.21
6	Sally	2/03/1944	02.03
7	Billy	5/24/1978	05.24
8	Nancy	8/15/1955	08.15
9	Henry	10/10/1994	10.10
10	Linda	12/17/2012	12.17
Sheet1

Cell Formulas
Range	Formula
E5:E10	E5	=TEXT([@DoB],"mm.dd")

 

[SunnyAlv]

Try this

in Cells "C2"

=SORTBY(TEXT(B2:B7,"MM/DD"),MONTH(B2:B7),1,DAY(B2:B7),1)

in cells "E2"

=SORTBY(HSTACK(A2:A7,TEXT(B2:B7,"mm/dd")),MONTH(B2:B7),1,DAY(B2:B7),1)

 

[JenniferMurphy]

Try this

in Cells "C2"

=SORTBY(TEXT(B2:B7,"MM/DD"),MONTH(B2:B7),1,DAY(B2:B7),1)

in cells "E2"

=SORTBY(HSTACK(A2:A7,TEXT(B2:B7,"dd/mm")),MONTH(B2:B7),1,DAY(B2:B7),1)

View attachment 106189

Hmmm... What I see in the quote above in my reply is quite different from what I see in your reply.

It looks like your solution also required the helper column, no?

 

[SunnyAlv]

Hmmm... What I see in the quote above in my reply is quite different from what I see in your reply.

It looks like your solution also required the helper column, no?

it doesn't need a helper column just convert the date to an array(MM/DD) then sort it by Month & Day.

 

[JenniferMurphy]

Ok. I'll play with it. Thanks

 

[Dave Patton]

Two quick thoughts and I hope you receive some alternatives.
The first requires the helper column and the sort may not be appropriate.

Kim 202312.xlsx
	C	D	E
1
2
3	Sorted by Column E
4	Name	DoB	m/dd
5	Joe	21-Jan-80	121
6	Sally	03-Feb-44	203
7	Billy	24-May-78	524
8	Nancy	15-Aug-55	815
9	Henry	10-Oct-94	1010
10	Linda	17-Dec-12	1217
11
12	Formula solution
13	Joe	21-Jan-80
14	Sally	03-Feb-44
15	Billy	24-May-78
16	Nancy	15-Aug-55
17	Henry	10-Oct-94
18	Linda	17-Dec-12
1dd

Cell Formulas
Range	Formula
E5:E10	E5	=--TEXT(D5,"mmdd")
C13:C18	C13	=XLOOKUP(D13:D18,$D$5:$D$10,$C$5:$C$10)
D13:D18	D13	=SORTBY(D5:D10,--TEXT(D5:D10,"mmdd"))
Dynamic array formulas.

 

[Dave Patton]

try the following

Kim 202312.xlsx
	B	C	D
20		Formula solution	DOB
21		Joe	21-Jan-80
22		Sally	03-Feb-44
23		Billy	24-May-78
24		Nancy	15-Aug-55
25		Henry	10-Oct-94
26		Linda	17-Dec-12
1dd

Cell Formulas
Range	Formula
C21:D26	C21	=SORTBY(C5:D10,--TEXT(D5:D10,"mmdd"))
Dynamic array formulas.

 

[alansidman]

An alternative with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DoB", type date}}),
    #"Inserted Text Before Delimiter" = Table.AddColumn(#"Changed Type", "Text Before Delimiter", each Text.BeforeDelimiter(Text.From([DoB], "en-US"), "/", {0, RelativePosition.FromEnd}), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Text Before Delimiter",{"DoB"})
in
    #"Removed Columns"

 

[Dave Patton]

The formula can work with the text =SORTBY(C5:D10,TEXT(D5:D10,"mmdd")) or with Table

Kim 202312.xlsx
	C	D	E	F	G
1
2
3
4	Name	DOB
5	Billy	24-May-78		Joe	21-Jan-80
6	Henry	10-Oct-94		Sally	03-Feb-44
7	Joe	21-Jan-80		Billy	24-May-78
8	Linda	17-Dec-12		Nancy	15-Aug-55
9	Nancy	15-Aug-55		Henry	10-Oct-94
10	Sally	03-Feb-44		Linda	17-Dec-12
11
1dd

Cell Formulas
Range	Formula
F5:G10	F5	=SORTBY(Table2,TEXT(Table2[DOB],"mmdd"))
Dynamic array formulas.

 

[JenniferMurphy]

Two quick thoughts and I hope you receive some alternatives.
The first requires the helper column and the sort may not be appropriate.

Kim 202312.xlsx
	C	D	E
1
2
3	Sorted by Column E
4	Name	DoB	m/dd
5	Joe	21-Jan-80	121
6	Sally	03-Feb-44	203
7	Billy	24-May-78	524
8	Nancy	15-Aug-55	815
9	Henry	10-Oct-94	1010
10	Linda	17-Dec-12	1217
11
12	Formula solution
13	Joe	21-Jan-80
14	Sally	03-Feb-44
15	Billy	24-May-78
16	Nancy	15-Aug-55
17	Henry	10-Oct-94
18	Linda	17-Dec-12
1dd

Cell Formulas
Range	Formula
E5:E10	E5	=--TEXT(D5,"mmdd")
C13:C18	C13	=XLOOKUP(D13:D18,$D$5:$D$10,$C$5:$C$10)
D13:D18	D13	=SORTBY(D5:D10,--TEXT(D5:D10,"mmdd"))
Dynamic array formulas.

How is your first example with the helper column different than my original solution?