If you have a list of birthdays in Excel, you might want to sort them by month to see who has a birthday in each month, or to send birthday wishes to your contacts. However, sorting by date will not give you the desired result, as it will also take into account the day and the year. In this article, we will show you how to use the MONTH and DAY functions to sort birthdays by month in Excel, regardless of the day and the year.
Basic Theory
The MONTH and DAY functions are two of the many date and time functions in Excel. They return the month and the day of a given date, respectively, as numbers. For example, if A1 contains the date 14/02/2024, then =MONTH(A1) will return 2, and =DAY(A1) will return 14.
We can use these functions to create helper columns that extract the month and the day from the birthday column. Then, we can sort the data by these helper columns, first by month, and then by day, to get the birthdays sorted by month.
Procedures
To sort birthdays by month in Excel, follow these steps:
- Assume that you have a list of names and birthdays in columns A and B, starting from row 2. Insert two new columns, C and D, and name them Month and Day, respectively.
- In cell C2, enter the formula
=MONTH(B2)and press Enter. This will return the month of the birthday in cell B2 as a number. Drag the formula down to fill the rest of the column C. - In cell D2, enter the formula
=DAY(B2)and press Enter. This will return the day of the birthday in cell B2 as a number. Drag the formula down to fill the rest of the column D. - Select the entire data range, from A2 to D11 (or the last row of your data). Go to the Data tab, and click on Sort. In the Sort dialog box, choose the following options:
- Sort by: Month
- Sort on: Values
- Order: Smallest to Largest
- Then by: Day
- Sort on: Values
- Order: Smallest to Largest
- Click OK to apply the sorting. You will see that the birthdays are now sorted by month, and within each month, by day.
Example
To illustrate the above steps, let’s use the following sample data:
| Name | Birthday |
|---|---|
| Alice | 14/02/2024 |
| Bob | 23/05/2024 |
| Charlie | 01/01/2024 |
| David | 31/10/2024 |
| Eve | 29/02/2024 |
| Frank | 15/08/2024 |
| Grace | 07/04/2024 |
| Harry | 25/12/2024 |
| Irene | 16/06/2024 |
| Jack | 09/09/2024 |
After following the steps above, we will get the following result:
| Name | Birthday | Month | Day |
|---|---|---|---|
| Charlie | 01/01/2024 | 1 | 1 |
| Alice | 14/02/2024 | 2 | 14 |
| Eve | 29/02/2024 | 2 | 29 |
| Grace | 07/04/2024 | 4 | 7 |
| Bob | 23/05/2024 | 5 | 23 |
| Irene | 16/06/2024 | 6 | 16 |
| Frank | 15/08/2024 | 8 | 15 |
| Jack | 09/09/2024 | 9 | 9 |
| David | 31/10/2024 | 10 | 31 |
| Harry | 25/12/2024 | 12 | 25 |
As you can see, the birthdays are sorted by month, and within each month, by day.
Other Approaches
There are other ways to sort birthdays by month in Excel, such as using the TEXT function, the DATEVALUE function, or a custom list. However, the MONTH and DAY functions are simple and easy to use, and they work well for this purpose. You can also format the helper columns to show the month names or abbreviations, instead of numbers, if you prefer. To do that, select the Month column, go to the Home tab, and click on the Number Format drop-down. Choose Custom, and enter mmm or mmmm in the Type box. Click OK to apply the formatting.
