Excel is rich in data sorting features - you have built in features like Sort and functions like SORT or SORTBY. However, these methods won't work without some modification when you need to sort dates in Excel. In this article, we are going to show you how to sort birthdays in Excel (or any dates).
Logic
The logic behind of sorting by dates is based on sorting by dates, excluding years. Thus, you want to sort by month and day. One approach is to create a helper column which contains month and day information in a sort-able format. And other is to use the SORTBY function, which can handle arrays and removes the need of a helper column.
You can choose either one which fits most to you. However, you need to be aware that the SORTBY function is only available for Microsoft 365 (formerly Office 365) subscribers.
Sorting birthdays by month with a helper column
This is a generic approach which you can execute on any Excel version. Let’s say we have a list of people with birthdays.
Obviously, the Sort feature doesn’t have an option for month ordering specifically. Thus, we can use a helper column of month and day values and sort by that column.
To get both month and day values in one cell, you have couple of options:
- TEXT function which can return month and day values according to given number formatting For example: “MM DD”, “MM DD”, “MM-DD”, MM.DD”, “MM/DD”.
- MONTH and DAY functions to return month and day numbers respectively. You need to multiply month value by 100 before adding with day to create a “MMDD” form: MONTH(B2) * 100 + DAY(B2)
In this article, we are going to be using the TEXT function approach. Although, there is a slash character (/) as a separator between month and day, you can select something else, like space ( ), dot (.) or dash (-), or omit a separator altogether.
Next step is to use Excel’s Sort feature on the helper column. Creating a helper column adjacent to source table will help you sort data in a few steps.
- Select a cell in the helper column
- Click either Sort A to Z or Sort Z or A icons to sort in desired order
Here is the result after sorted A to Z:
Alternatively, you can use the Sort dialog to sort based on more than one criteria. Detailed information sample can be found in the How to sort in Excel article.
Sorting birthdays with the SORTBY Function
SORTBY function is one of the dynamic array functions that have been released recently. If you are a Microsoft 365 subscriber, you can sort birthdays with this formula. Of course, in order to use a formula you need to populate more columns. This approach is good if you don’t want to change your source data.
SORTBY function simply sorts an array by given arrays in the corresponding sorting order. The trick is to use the TEXT function with the date column as a sorting array.
What you need to do is pretty straightforward.
- (Optional) You may want to create headers for the target table which will populated by SORTBY
- Select the top left cell of target table
- Enter the formula =SORTBY(<range of while table>, TEXT(<range of date column),<number formatting>)
Excel will populate cells automatically:
