atlas

Tom’s Tutorials For Excel: Sorting Birthdays, Excluding the Year

Companies and organizations often keep a list of their members’ birth dates, for purposes of sending out birthday greetings or managing employees’ birthdays with a complimentary day off work.



As the above picture shows, the challenge is to arrange the list by month and day to coincide with the same sequence of birthdays as they occur during the calendar year. It is not a simple matter of sorting the list by the Date Of Birth column, because the year of birth is not relevant.

In the next pictures, column C serves as a helper column.
In cell C5 and copied down is the formula =TEXT(B5,"MM DD").
After selecting the range, Press Alt+D+S to call the Sort dialog box.
Sort by column C in ascending order.



The final result is this list after the helper formulas in column C were deleted.



This same task can be automated, for example by the following macro:

Sub SortBirthdays()
'Turn off ScreenUpdating.
Application.ScreenUpdating = False
'Declare a Long variable for the last row in column B.
Dim LastRow As Long
'Determine the last row of data in column B.
LastRow = Cells(Rows.Count, 2).End(xlUp).Row
'Enter the helper formula =TEXT($B5,"MM DD")
Range("C5:C" & LastRow).FormulaR1C1 = "=TEXT(RC2,""MM DD"")"
'Sort the table by column C.
Range("A4:C" & LastRow).Sort _
Key1:=Range("C5"), Order1:=xlAscending, Header:=xlYes
'Clear column C of the helper formula.
Range("C5:C" & LastRow).Clear
'Turn ScreenUpdating on again.
Application.ScreenUpdating = True
End Sub