Exceljet

Quick, clean, and to the point

Filter on upcoming birthdays

Excel formula: Filter on upcoming birthdays
Summary 

To show the next n upcoming birthdays in a set of data that includes birth dates, you can use a formula based on the INDEX, FILTER, and SORTBY functions, with help from NOW, TEXT, SEQUENCE, and LET. In the example shown, the formula in E5 is:

=LET(mdays,--TEXT(bdays,"m.dd"),tday,--TEXT(NOW(),"m.dd"),INDEX(SORTBY(FILTER(data,mdays>=tday),FILTER(mdays,mdays>=tday)),SEQUENCE(7),SEQUENCE(1,COLUMNS(data))))

where data (B5:C29) and bdays (C5:C29) are named ranges for convenience only. This formula returns the next 7 birthdays in the data based on the current date, which is April 3, 2021 in the example shown.

Note: This formula displays upcoming birthdays. To sort and display all birthdays, see this formula.

Explanation 

The goal of this formula is to return the next n birthdays in a set of 25 birthdays as shown, based on the current date. The complication is that the birthdays contain the year of birth, so we can't filter and sort the data without taking this into account. To reduce redundancy, the LET function is used to define two variables, mdays, and tday:

mdays,--TEXT(bdays,"m.dd")
tday,--TEXT(NOW(),"m.dd")

Both variables are defined using the TEXT function, in order to apply a number format. The variable mdays defines an array with all birthdays converted into a numeric month.day format like 1.01 for January 1, and 3.18 for March 18. The variable tday holds a single month.day value for the current date in the same format. The double negative (--) is used to coerce the text value returned by TEXT into a number.

Next, we have the calculation returned by the LET function:

INDEX(SORTBY(FILTER(data,mdays>=tday),FILTER(mdays,mdays>=tday)),SEQUENCE(7),SEQUENCE(1,COLUMNS(data)))

Working from the inside out, the core filter operation is performed by the FILTER function here:

FILTER(data,mdays>=tday) // future birthdays

FILTER returns an array of rows in data where the birthday is greater than or equal to the current date. Because we want to display birthdays in the order they will come up, we hand off this result to the SORTBY function:

SORTBY(FILTER(data,mdays>=tday),FILTER(mdays,mdays>=tday))

The first argument to SORTBY is the data returned by FILTER above. The second argument is a filtered array of mdays greater than or equal to the current date:

FILTER(mdays,mdays>=tday) // mdays today or later

We need to perform this step because SORTBY requires that "sort by" array have dimensions compatible with the array being sorted. The result from SORTBY are the rows in the data that represent future birthdays, sorted in the order that the birthdays will occur. This array is handed off to the INDEX function, which is used to limit the output to the first seven upcoming birthdays:

INDEX(sortby_result,SEQUENCE(7),SEQUENCE(1,COLUMNS(data)))

The SEQUENCE function is used to create the arrays needed for row number and column number inside INDEX. Once SEQUENCE runs, we have:

INDEX(sortby_result,{1;2;3;4;5;6;7},{1,2})

and INDEX returns the first 7 rows in the data. The number 7 inside SEQUENCE given as row number to INDEX can be changed as desired.

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.