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

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

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,
17 comments on “Tom’s Tutorials For Excel: Sorting Birthdays, Excluding the Year
  1. Joseph says:

    That’s a nice trick there. I haven’t thought about doing it in that way. You could even leave another column in there simply called “Birthday” with only the month and day if the user isn’t picky about a little bit of redundant data 🙂

    • Tom Urtis says:

      Thanks Joseph. In fact as you suggested with the extra column, I have seen companies’ marketing departments keep a record of their clients’ birthdays or business relationship anniversary dates. Thanks for visiting my blog page, and for your retweets…much appreciated!

  2. What about a helper column with a formula like below to keep an aye on [both small and big] anniversaries?
    DATEDIFF(D53;TODAY();”y”)+1={25;30;35;40;45;50;55;60;65;70;75;80;85}));”Round age!”;””)

  3. Charmaine says:

    Very helpful…dates are always a pain to work with.
    Thank you for sharing your knowledge.

  4. Annette White says:

    Thanks so much that has made it so much easier to be able to sort to be able to do in two batches per month!

  5. jayjaymartin says:

    I would prefer a solution without the need for an add-in, even if it is readily available.

    That said, could you explain the edate and datedif functions, as I’ve not experienced them before?

  6. Patsy says:

    Thank you for this tutorial!

  7. Mary Juanna says:

    thank you, this was the quickest & easiest i’ve ever seen! so helpful… btw, hilarious names you used!

  8. Sue says:

    We are having problems with an employee’s anniversary being on February 29. It will not sort. Any suggestions?

    • Tom Urtis says:

      What problem are you having exactly? I just manually created a new list with various dates including Feb 29, 2016 and it sorted perfectly with the helper formula.

  9. Sue says:

    His anniversary date is 2/29/2016; however, it was leaving him at the bottom of the list. I believe I just found the issue. I was sorting with the option: sort anything that looks like a number as a number instead of sort numbers and numbers stored as text separately. When I sorted with the second option, it worked correctly. (The numbers are formatted as dates.)

  10. Mark Higgins says:

    I need to be able to determine which pupils birthdays fall within two dates. I have the pupils name, dates of birth and term dates. I would like the formula to return the pupils name if the birthday falls within the date range.

    • Tom Urtis says:

      How is your worksheet organized in terms of the two dates the birthday would fall between? Are those dates each in some cell, and if so what cells.

Leave a Reply

Your email address will not be published. Required fields are marked *