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

We have a sample dataset where the birthdays are not arranged orderly.

Method 1 – Using the Combination of MONTH and DAY Functions

To sort the birthdays by months and days ignoring years, arrange the dates from January to May and the days within these months will be arranged in ascending order.

Steps:
➤ Enter the following formula in cell E4.

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.

It will output 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 will give the serial number of the days of the dates in the Birthday column.

Sort the values of the Month column and the values of the Day column in an ascending order to arrange the birthdays serially by month and then day.
➤ Select the dataset and go to the Home Tab >> Editing Group >> Sort & Filter Dropdown >> Custom Sort Option.

➤ Click on the My data has headers option and select the following in the Sort by box,
Column → Month
Sort On → Cell Values
Order → Smallest to Largest

Add the Day column to sort the days’ values after sorting the months.
➤ Select the Add Level option to add the second sorting field.

➤ Select the following in the Then by box,
Column → Day
Sort On → Cell Values
Order → Smallest to Largest
➤ Press OK.

The dataset is sorted based on the birthdays as shown in the image below.

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

U

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 in the sample dataset.

Steps:
➤ Enter the following formula in cell E4.

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.

It will output the combined month and day values in text format in the Text column.

Convert the texts into values by using the following formula

VALUE will convert the text string in E4 into a numeric value.

To sort the values of the given dataset based on the Value column.
➤ Select the dataset and go to the Home Tab >> Editing Group >> Sort & Filter Dropdown >> Custom Sort Option.

➤ Click on the My data has headers option and select the following in the Sort by box,
Column → Value
Sort On → Cell Values
Order → Smallest to Largest
➤ Press OK.

The birthdays will be sorted by month and day.

Read More: How to Sort by Month in Excel

Method 3 – Arranging Birthdays by Using the DATE Function

Steps:
➤ Enter the following formula in cell E4.

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.

The year will be the same for all, but with the corresponding months and days of the dates of the Birthday column.

To sort the values of the following dataset based on the Helper column,
➤ Select the dataset and go to the Home Tab >> Editing Group >> Sort & Filter Dropdown >> Custom Sort Option.

➤ Click on the My data has headers option and select the following in the Sort by box,
Column → Helper
Sort On → Cell Values
Order → Oldest to Newest
➤ Press OK.

The dataset is sorted based on the birthdays as shown in the image below.

Read More: How to Sort Dates in Chronological Order in Excel

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

Steps:
➤ Enter the following formula in cell E4.

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.

Press ENTER to get the results.

The SORTBY function is only available for the Microsoft Excel 365 version.

Read More: How to Sort Dates in Excel by Month and Year

Method 5 – Using VBA Code

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

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

A Module will be created.

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

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.
We used two For Next 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.
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, the new dates and their corresponding employee id and name of the following cell will take place to the previous cells.

➤ Press F5.
The data will be sorted based on the birthdays by month and day.

Read More: How to Sort by Date in Excel

Download Practice Workbook

Related Articles

• How to Sort Dates in Excel by Year
• How to Sort by Date and Time in Excel

<< Go Back to Sort by Date in Excel | Sort in Excel | Learn Excel