Exceljet

Quick, clean, and to the point

Sort birthdays by month and day

Excel formula: Sort birthdays by month and day
Summary 

To sort a list of birthdays that include the year of birth by month and day, you can use a formula based on the SORTBY function together with the TEXT function. 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 result is the list of birthdays sorted by month and day, ignoring year.

Explanation 

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. This is 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.

Numeric sort variation

The formula explained above is based on creating text values TEXT that can be used for sorting. For the purposes of this example, this works fine. However, there are cases 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.

Dynamic Array Formulas are available in Excel 365 only.
Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.