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.
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
- Auto Sort in Excel Without Macros
- How to Sort in Excel by Number of Characters
- Excel Sort by Column Without Header
- Excel Sort Column by Value
- How to Sort Multiple Columns in Excel Independently of Each Other
- How to Sort Alphabetically in Excel with Multiple Columns
- How to Perform Custom Sort in Excel
- How to Add Sort Button in Excel
- How to Sort by Last Name in Excel
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.
Read More: Excel Auto Sort when Data is Entered
Similar Readings
- Excel Auto Sort when Data Changes
- How to Sort Data in Excel by Value
- How to Sort Data in Alphabetical Order in Excel
- How to Put Numbers in Numerical Order in Excel
- How to Sort Alphanumeric Data in Excel
- How to Sort Multiple Columns in Excel
- How to Auto Sort Multiple Columns in Excel
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
- How to Sort Two Columns in Excel to Match
- Excel Sort by Row not Column
- How to Sort Rows in Excel
- Excel Sort Rows by Column
- How to Sort Alphabetically in Excel and Keep Rows Together
- How to Sort by Date in Excel
- How to Sort Data in Excel Using Formula
- How to Sort Dates in Excel by Year
- Excel Sort by Date and Time
- How to Sort Numbers with Letter Suffix in Excel
- How to Sort Dates in Chronological Order in Excel
- How to Sort by Month in Excel
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.
Read More: How to Sort Dates in Excel by Month and Year
Similar Readings
- How to Sort Excel Sheet by Date
- Sort IP Address in Excel
- Random Sort in Excel
- How to Sort Excel Tabs
- How to Sort by Color in Excel
- How to Remove Sort by Color in Excel
- Advanced Sorting in Excel
- How to Sort Duplicates in Excel
- Excel Sort Unique
- How to Sort Numbers in Excel
- How to Auto Sort Table in Excel
- Advantages of Sorting Data in Excel
- Difference Between Sort and Filter in Excel
- Sort and Filter in Excel Not Working
- Excel Not Sorting Numbers Correctly
- Excel Sort by Cell Color Not Working
- Excel Sort Largest to Smallest Not Working
- How to Sort by Name in Excel
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.
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
- Excel Sort Not Working
- How to Sort Drop Down in Excel
- How to Sort Merged Cells in Excel
- How to Sort Merged Cells of Different Sizes in Excel
- Sorting Columns in Excel While Keeping Rows Together
- How to Use Excel Shortcut to Sort Data
- How to Sort by Ascending Order in Excel
- [Fix] Excel Sort by Date Not Working
- Excel Sort and Ignore Blanks
- How to Sort Columns in Excel Without Mixing Data
- How to Arrange Numbers in Ascending Order with Excel Formula