How to Sort Birthdays by Month and Day in Excel (5 Ways)

Get FREE Advanced Excel Exercises with Solutions!

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 in 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 learn deeply about the procedures.

Table of Contents hide

How to Sort Birthdays by Month and Day in Excel (5 Easy Ways)

Method-1: Using the Combination of MONTH and DAY Functions

Method-2: Using the TEXT and VALUE Functions to Sort Birthdays by Month and Day

Method-3: Arranging Birthdays by Using the DATE Function

Method-4: Sort Birthdays by Month and Day Using the SORTBY and TEXT Functions

Method-5: Using VBA Code

Practice Section

Conclusion

How to Sort Birthdays by Month and Day in Excel (5 Easy Ways)

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 the Microsoft Excel 365 version here, you can use any other version 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 Undo Sort in Excel

Similar Readings

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 to sort 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.

Similar Readings

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 of the same year but with the corresponding months and days of the dates of the Birthday column.

It’s time to sort 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 and Filter Data in Excel

Similar Readings

Method-4: Sort Birthdays by Month and Day Using the SORTBY and TEXT Functions

Here, we will use the SORTBY function and the 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 the Microsoft Excel 365 version.

Similar Readings

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 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: How to Remove Sort in Excel

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.

Download Practice Workbook

You can download the workbook and practice with them.

Sorting Birthdays.xlsm

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.

How to Sort Birthdays by Month and Day in Excel (5 Ways)

How to Sort Birthdays by Month and Day in Excel (5 Easy Ways)

Method-1: Using the Combination of MONTH and DAY Functions

Method-2: Using the TEXT and VALUE Functions to Sort Birthdays by Month and Day

Method-3: Arranging Birthdays by Using the DATE Function

Method-4: Sort Birthdays by Month and Day Using the SORTBY and TEXT Functions

Method-5: Using VBA Code

Practice Section

Conclusion

Related Articles

Leave a reply Cancel reply