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
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 🙂
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!
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!”;””)
Very helpful…dates are always a pain to work with.
Thank you for sharing your knowledge.
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!
My pleasure Annette! Thank you for your kind message, and for following my Excel blog.
Tom
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?
What add-in are you talking about? My post here shows a manual method and a VBA method. You choose which method you want. No add-ins or special code is needed, nor was shown.
Take a look at Help for examples of the EDATE and DATEDIF functions. This page on my website explains the DATEDIF function, which was undocumented before version 2013 but is documented now.
http://www.atlaspm.com/toms-tutorials-for-excel/toms-tips-for-excel-how-old-are-you-with-undocumented-datedif/
Thank you for this tutorial!
thank you, this was the quickest & easiest i’ve ever seen! so helpful… btw, hilarious names you used!
Thanks!
Tom,
We are having problems with an employee’s anniversary being on February 29. It will not sort. Any suggestions?
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.
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.)
Thanks for the follow-up, Sue.
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.
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.