If you are looking for some of the easiest ways to sort birthdays in Excel by month and day, then this article will be helpful for you. Grouping birthdays according to the months serially and then arranging days with an ascending or descending order may help you notice whose birthday is in which month and on which day serially.
So, let’s start with this article to know deeply about the procedures.
Download Workbook
5 Ways to Sort Birthdays by Month and Day in Excel
Here, we have some employees’ records with their ids and birthdays a company. But these birthdays are not arranged orderly. To sort the following dataset depending on the birthdays grouped by months and then days serially we will apply the following 5 methods.
We have used Microsoft Excel 365 version here, you can use any other versions according to your convenience.
Method-1: Using the Combination of MONTH and DAY Functions
Here, we will sort the birthdays by months and days ignoring years which means we will first arrange the dates from January to May, and then the days within these months will be arranged from lower values to higher values which means in ascending order.
For this purpose, we are going to use the combination of the MONTH function and DAY function and we have included two columns Month and Day for retaining the values.
Steps:
➤ Type the following formula in cell E4.
=MONTH(D4)
MONTH will give the serial number of the months of the dates in the Birthday column.
➤ Press ENTER and drag down the Fill Handle tool.
Then, you will have the serial number of months of the corresponding birthdays.
To extract the days from the dates of the Birthday column, we will use the following formula
=DAY(D4)
DAY will give the serial number of the days of the dates in the Birthday column.
Now, we will sort the values of the Month column first and then the values of the Day column in an ascending order to arrange the birthdays serially by month and then day.
➤ Select the dataset, and then, go to the Home Tab >> Editing Group >> Sort & Filter Dropdown >> Custom Sort Option.
After that, the Sort dialog box will open up.
➤ Click on the My data has headers option and then select the following in the Sort by box
Column → Month
Sort On → Cell Values
Order → Smallest to Largest
Now, we will add the Day column to sort the days’ values after sorting the months.
➤ Select the Add Level option to add the second sorting field.
Then, you will get the Then by box for adding the second column to sort.
➤ Select the following in the Then by box
Column → Day
Sort On → Cell Values
Order → Smallest to Largest
➤ Press OK.
Finally, we will have the sorted dataset based on the birthdays as you can see we have grouped the January, February, March, and May months with the days arranged serially.
Read More: How to Sort Data in Excel Using Formula
Method-2: Using the TEXT and VALUE Functions to Sort Birthdays by Month and Day
Using the TEXT function and VALUE function we will combine the serial numbers of the month and day of the dates of the Birthday column and for this purpose, we have added the Text and the Value column. Then, we will conduct the sorting procedures based on the Value column.
Steps:
➤ Type the following formula in cell E4.
=TEXT(D4,"mdd")
TEXT will change the format of the date in D4 in mdd format where m means month and d means day.
➤ Press ENTER and drag down the Fill Handle tool.
Then, you will have the combined month and day values in text format in the Text column.
Now, we will convert the texts into values by using the following formula
=VALUE(E4)
VALUE will convert the text string in E4 into a numeric value.
It’s time for sorting the values of the following dataset based on the Value column now.
➤ Select the dataset, and then, go to the Home Tab >> Editing Group >> Sort & Filter Dropdown >> Custom Sort Option.
After that, the Sort dialog box will appear.
➤ Click on the My data has headers option and then select the following in the Sort by box
Column → Value
Sort On → Cell Values
Order → Smallest to Largest
➤ Press OK.
Eventually, you will be able to sort birthdays by month and day in Excel.
Read More: How to Sort by Month in Excel (4 Methods)
Method-3: Arranging Birthdays by Using the DATE Function
Here, we will sort the birthdays by month and day ignoring year with the help of the DATE function.
Steps:
➤ Type the following formula in cell E4.
=DATE(2020,MONTH(D4),DAY(D4))
Here, 2020 is a random year by using this year we will have the same year for all of the dates in the Date column and so we will be able to ignore the year while sorting.
MONTH will extract the month value of the date in D4 and DAY will give the day of the date in D4.
➤ Press ENTER and drag down the Fill Handle tool.
Then, you will have the dates with the same year but with the corresponding months and days of the dates of the Birthday column.
It’s time for sorting the values of the following dataset based on the Helper column now.
➤ Select the dataset and then go to the Home Tab >> Editing Group >> Sort & Filter Dropdown >> Custom Sort Option.
After that, the Sort dialog box will appear.
➤ Click on the My data has headers option and then select the following in the Sort by box
Column → Helper
Sort On → Cell Values
Order → Oldest to Newest
➤ Press OK.
Ultimately, we will have the sorted dataset based on the birthdays as you can see we have grouped the January, February, March, and May months with the days arranged serially.
Read More: How to Sort Excel Sheet by Date (8 Methods)
Similar Readings
- How to Sort ListBox with VBA in Excel (A Complete Guide)
- How to Undo Sort in Excel (3 Methods)
- Excel Sort and Ignore Blanks (4 Ways)
- How to Sort Excel Tabs in Ascending or Descending Order (2 Ways)
- How to Sort Data by Row Not Column in Excel (2 Easy Methods)
Method-4: Sort Birthdays by Month and Day Using the SORTBY and TEXT Functions
Here, we will use the SORTBY function and TEXT function to sort birthdays in Excel by month and day in the quickest manner.
Steps:
➤ Type the following formula in cell E4.
=SORTBY(B4:C13,TEXT(C4:C13,"mdd"))
B4:C13 is the range on which we want to apply the sorting and TEXT(C4:C13,”mdd”) is the range of the combination of the month and day from the range C4:C13 based on which we will do our sorting procedure.
After pressing ENTER, you will get the sorted results immediately.
The SORTBY function is only available for Microsoft Excel 365 version.
Read More: How to Use Excel Shortcut to Sort Data (7 Easy Ways)
Method-5: Using VBA Code
In this section, we are going to use a VBA code to sort birthdays by month and day.
Steps:
➤ Go to the Developer Tab >> Visual Basic Option.
Then, the Visual Basic Editor will open up.
➤ Go to the Insert Tab >> Module Option.
After that, a Module will be created.
➤ Write the following code
Sub sorting_bdays_by_m_d()
Dim initial_month, initial_day, new_month, new_day As Integer
Dim initial_date As Date, initial_employee As String
Dim initial_ID As Long
For k = 4 To 13
For l = k + 1 To 13
initial_month = Month(Cells(k, 4).Value)
initial_day = Day(Cells(k, 4).Value)
new_month = Month(Cells(l, 4).Value)
new_day = Day(Cells(l, 4).Value)
If (new_month < initial_month) Or (new_month = initial_month _
And new_day < initial_day) Then
initial_date = Cells(k, 4).Value
Cells(k, 4).Value = Cells(l, 4).Value
Cells(l, 4).Value = initial_date
initial_employee = Cells(k, 3).Value
Cells(k, 3).Value = Cells(l, 3).Value
Cells(l, 3).Value = initial_employee
initial_ID = Cells(k, 2).Value
Cells(k, 2).Value = Cells(l, 2).Value
Cells(l, 2).Value = initial_ID
End If
Next l
Next k
End Sub
Here, we have declared initial_month, initial_day, new_month, new_day as Integer, initial_date as Date, initial_employee as String, and initial_ID as Long.
Then we have used two FOR loops one is for k= 4 to 13 (start and end row number of the dataset) and another is for l = k+ 1 to 13 to check the next values of k.
initial_month, initial_day will store the month and day value of a cell within the limit of k, and new_month, new_day will store the month and day value of the corresponding next cell for limit l.
Then IF statement will check if the new_month is less than initial_month or if they are equal then new_day should be less than initial_day and if the condition is fulfilled then the new dates and their corresponding employee id and name of the following cell will take place to the previous cells.
➤ Press F5.
Then, you will have the sorted dataset based on the birthdays by month and day.
Read More: VBA to Sort Table in Excel (4 Methods)
Practice Section
For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.
Conclusion
In this article, we tried to cover the ways to sort birthdays in Excel by month and day easily. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.
Related Articles
- How to Sort Alphanumeric Data in Excel (With Easy Steps)
- Excel Not Sorting Numbers Correctly (4 Reasons with Solutions)
- How to Auto Sort in Excel without Macros (3 Methods)
- Excel Sort by Column without Header (5 Methods)
- [Fixed]: Sort by Cell Color Not Working in Excel (3 Solutions)
- How to Sort in Excel by Number of Characters (2 Methods)