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

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.

how to sort birthdays in Excel by month and day

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.

how to sort birthdays in Excel by month and day

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.

MONTH and DAY functions

➤ Press ENTER and drag down the Fill Handle tool.

MONTH and DAY functions

Then, you will have the serial number of months of the corresponding birthdays.

how to sort birthdays in Excel by month and day

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.

MONTH and DAY functions

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.

MONTH and DAY functions

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

how to sort birthdays in Excel by month and day

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.

MONTH and DAY functions

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.

MONTH and DAY functions

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.

how to sort birthdays in Excel by month and day

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.

how to sort birthdays in Excel by month and day

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.

TEXT and VALUE functions

➤ Press ENTER and drag down the Fill Handle tool.

TEXT and VALUE functions

Then, you will have the combined month and day values in text format in the Text column.

how to sort birthdays in Excel by month and day

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.

TEXT and VALUE functions

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.

TEXT and VALUE functions

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.

TEXT and VALUE functions

Eventually, you will be able to sort birthdays by month and day in Excel.

how to sort birthdays in Excel by month and day

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.

how to sort birthdays in Excel by month and day

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.

DATE Function

➤ Press ENTER and drag down the Fill Handle tool.

DATE Function

Then, you will have the dates with the same year but with the corresponding months and days of the dates of the Birthday column.

how to sort birthdays in Excel by month and day

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.

DATE Function

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.

DATE Function

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.

how to sort birthdays in Excel by month and day

Read More: How to Sort Excel Sheet by Date (8 Methods)


Similar Readings


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.

how to sort birthdays in Excel by month and day

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.

SORTBY and TEXT Functions

After pressing ENTER, you will get the sorted results immediately.

SORTBY and TEXT Functions

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.

how to sort birthdays in Excel by month and day

Steps:
➤ Go to the Developer Tab >> Visual Basic Option.

VBA Code

Then, the Visual Basic Editor will open up.
➤ Go to the Insert Tab >> Module Option.

how to sort birthdays in Excel by month and day

After that, a Module will be created.

VBA Code

➤ 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.

VBA Code

➤ Press F5.
Then, you will have the sorted dataset based on the birthdays by month and day.

how to sort birthdays in Excel 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.

practice


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

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo