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")

 

[JenniferMurphy]

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"

I have been meaning to get up to speed with Power Query. This will give me another incentive. Thanks

 

[JenniferMurphy]

Thanks to everyone for the suggestions. I am going to want to sort the table dynamically on different columns, so I think I am going to stick with my original solution. That way I can right-click a column and select sort.

 

[kevin9999]

Just for fun Jennifer, I thought I'd look at a VBA option. Here it is to play with:

Option Explicit
Sub JenMurphy()
    Dim a, tmp, i As Long, j As Long
    a = ActiveSheet.ListObjects("Table1").DataBodyRange     '<-- *** Just check the Table name ***
    ReDim Preserve a(1 To UBound(a, 1), 1 To 4)
    Dim r As Range, c As Range
    Set r = Range("D5", Cells(Rows.Count, "D").End(xlUp))
    
    i = 1
    For Each c In r
        a(i, 4) = Evaluate(CLng(c) & "-DATE(YEAR(" & CLng(c) & "),1,1)+1")
        i = i + 1
    Next c
    For i = 1 To UBound(a, 1)
        For j = i + 1 To UBound(a, 1)
            If a(i, 4) > a(j, 4) Then
                tmp = a(j, 1): a(j, 1) = a(i, 1): a(i, 1) = tmp
                tmp = a(j, 2): a(j, 2) = a(i, 2): a(i, 2) = tmp
                tmp = a(j, 3): a(j, 3) = a(i, 3): a(i, 3) = tmp
                tmp = a(j, 4): a(j, 4) = a(i, 4): a(i, 4) = tmp
            End If
        Next j
    Next i
    Range("C5").Resize(UBound(a, 1), 3) = a
End Sub

Before:

Book1
	C	D	E
4	Name	DoB	m/dd
5	Sally	2/03/1944	02.03
6	Nancy	8/15/1955	08.15
7	Billy	5/24/1978	05.24
8	Joe	1/21/1980	01.21
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")

After:

Book1
	C	D	E
4	Name	DoB	m/dd
5	Joe	1/21/1980	1.21
6	Sally	2/03/1944	2.03
7	Billy	5/24/1978	5.24
8	Nancy	8/15/1955	8.15
9	Henry	10/10/1994	10.1
10	Linda	12/17/2012	12.17
Sheet1

 

[JenniferMurphy]

Just for fun Jennifer, I thought I'd look at a VBA option. Here it is to play with:

Wow! That should keep me out of trouble for a month. Thanks

 

[Tetra201]

... Is there a better way that does not require the helper column?..

Just for fun: Yes, there is (not sure about "better" as it might take hours to set up) -- conditional formatting that assigns a specific fill color for each month and a specific font color for each date. Sorting by fill color then by font color. No helper column.

 

[JenniferMurphy]

Just for fun: Yes, there is (not sure about "better" as it might take hours to set up) -- conditional formatting that assigns a specific fill color for each month and a specific font color for each date. Sorting by fill color then by font color. No helper column.

This forum is an absolute gold mine of creative and diverse ways to solve any problem.