Sort birthdays by month and day

In this example, the goal is to sort a list of names and birthdays by month and year. The complication is that the birthdays also include a birth year, so if we try to sort the raw data by birthdays, we'll end up with a list of birthdays sorted first by year. We will actually see the oldest people in the list first, because Excel dates are actually serial numbers that increase over time.

To sort the data by month and then by day, ignoring year, we need to construct another array in memory that we can use for sorting. This is done with the TEXT function, which we use to extract the month and day. In the example shown, the formula in E5 is:

=SORTBY(data,TEXT(birthdays,"mmdd"))

where data (B5:C16) and birthdays (C5:C16) are named ranges.

The SORTBY function can sort by one or more arrays. One of the benefits the SORTBY function provides over the SORT function is the ability to sort by an array that is not part of the data. For the array argument, we provide the full set of data (B5:C16). For the sort_by argument, we use the TEXT function like this:

TEXT(birthdays,"mmdd")

The TEXT function is a way to convert a numeric value to a text value using a specific number format. In this case, the number format is "mmdd", which translates to a 2-digit month, followed by a 2-digit day (i.e. "0727" for the date in cell C5).

Because we give the TEXT function the full list of birthdays (C5:C16), which contains 12 dates, we get back 12 results in an array like this:

{"0727";"1025";"0116";"0425";"1126";"0203";"0826";"0907";"0512";"0323";"0607";"1210"}

Notice these values are actually text values (not numbers) but they work fine for the purpose of sorting in this case.

With this configuration, the SORTBY function returns all data sorted by the array returned by the TEXT function, and results spill into the range E5:F16, as seen in the example. Notice names and birthdays are now sorted by month, then by day. In other words, the birthdays are sorted in the order they will occur throughout the year.

Numeric sort variation

The formula explained above is based on creating text values that can be used for sorting. For the purposes of this example, this works fine. However, there are times where you might want or need a numeric sort option. In that case, you can use a variation like this:

=SORTBY(data,--TEXT(birthdays,"m.dd"))

The structure of this formula is the same as the formula above. However, the TEXT function is configured like this:

--TEXT(birthdays,"m.dd")

The number format "m.dd" will return a value like "7.27" for July 27, and "7.04" for July 4. We then use a double negative (--) to coerce the text value into a true number, which the SORTBY function uses to sort the data with the same result.

Older Excel versions

Because the SORTBY function is new in Excel 365, you won't find it in other Excel versions. In that case, the easiest way to solve this problem is to add a helper column to the original table with a formula like this, starting in cell D5:

=TEXT(C5,"mmdd")

Copy the formula down the table and name the helper column "Helper" in cell D4. Now you can use the "Helper" column to sort the table manually by month and day. It is possible to sort with a formula as well, but it is somewhat more complex. For more information on dynamic array alternatives, see: Alternatives to Dynamic Array Functions.