List upcoming birthdays

In this example, the goal is to list the next n upcoming birthdays from a larger set of 25 birthdays based on the current date. The set of birthdays are in an Excel Table named data in the range B5:C29. In the example shown, we are using 7 for n, so the result will be the next 7 upcoming birthdays, but this number can be changed as desired. The formula in E5 is:

=LET(
  n,7,
  tday,TODAY(),
  calendar,TEXT(SEQUENCE(EDATE(tday,12)-tday,1,tday),"mmdd"),
  birthdays,TEXT(data[Birthday],"mmdd"),
  sorted,SORTBY(data,XMATCH(birthdays,calendar)),
  INDEX(
    sorted,
    SEQUENCE(MIN(n,ROWS(data))),
    SEQUENCE(1,COLUMNS(data))
  )
)

The main challenge with a problem like this is to sort the list of birthdays into a rolling list of upcoming dates. This formula works in two parts: (1) sort all birthdays according to a rolling calendar and (2) return the first 7 birthdays from the sorted list.

Setup

To make this formula easier to read and more efficient, we are using the LET function to define variables that we need later in the calculation. We start off by defining n and tday:

=LET(
  n,7,
  tday,TODAY(),

Although n is used just once in the formula that follows, defining it here makes it easier to edit later. The tday variable is defined with the TODAY function, which returns the current date. The current date is used three times in the formula, so it makes sense to define the value as a variable.

Sorting the birthdays

For this formula to work, we need to sort the birthdays in an "upcoming order" based on the current date. There are two challenges that we need to overcome to sort the birthdays like this. First, the birthdates include a birth year, so if we try to sort these dates (which are just numbers underneath) as-is, the result will be birthdays sorted first by year, then by month and year, which won't work. The second challenge is that we need to sort the birthdays according to a rolling calendar year that always begins with the current date. The solution is to create a special sorting index based on day and month only, that begins on the current date.

Rolling calendar

To make a rolling calendar, we use the following snippet to define calendar:

calendar,TEXT(SEQUENCE(EDATE(tday,12)-tday,1,tday),"mmdd"),

Here we create a list of all dates in the next year with the SEQUENCE function:

SEQUENCE(EDATE(tday,12)-tday,1,tday) // next 365 days

Working from the inside out, we use the EDATE function to get a count:

EDATE(tday,12)-tday // count days

Starting with the current date, EDATE returns a date 12 months (1 year) in the future. From this date, the current date is subtracted. The result is a count of days between the current date and the same date next year. This is done to catch the 366 days that might occur in a leap year that includes February 29. In the example shown, the current year is 2021, and the following year is 2022, so the result is 365. This number is returned as the rows argument to SEQUENCE:

SEQUENCE(365,1,tday)

SEQUENCE then builds an array of 365 dates, beginning with tday (the current date). The resulting array is handed off to the TEXT function:

TEXT(SEQUENCE(EDATE(tday,12)-tday,1,tday),"mmdd")

Inside TEXT, value is the array returned by SEQUENCE, and format_text is provided as "mmdd". The result is an array of text values in the form "mmdd". So, for instance, the date November 2, 2021 becomes "1102". Notice the year value is simply discarded. Inside the LET function, the result from TEXT is assigned to the variable calendar.

Birthdays

At this point, we have a rolling calendar, calendar, but the birthdays in the Excel table still include a birth year. To sort the birthdays according to the calendar we created above, we need to process them with the TEXT function in the same way we created the rolling calendar above:

birthdays,TEXT(data[Birthday],"mmdd"),

The TEXT returns an array of text values in the same format as calendar, and this result is assigned to the variable birthdays.

Sorting the birthdays

At this point, we have what we need to sort the list of birthdays. The trick is we need to sort the birthdays by the order they will occur in the next year. To do this, we use the SORTBY function together with the XMATCH function:

sorted,SORTBY(data,XMATCH(birthdays,calendar)),

The entire table (data) is given to SORTBY as array. The by_array1 argument is generated with the XMATCH function like this:

XMATCH(birthdays,calendar) // get position of birthdays in calendar

XMATCH is used to get the position of each birthday inside the rolling calendar. XMATCH performs an exact match by default, so no other arguments are required. We want an exact match in this case because calendar is not sorted in ascending or descending order, but rather in the order the dates occur in the next year.

The result from XMATCH is an array of positions. Each number in this array is the position at which a birthday will occur in the rolling calendar. This result is provided to SORTBY as by_array1, which uses these positions to sort the birthday table in the same order as calendar. The result from SORTBY is assigned to the variable sorted.

Next n birthdays

At this point, we have a list of upcoming birthdays sorted in the order that they will occur over the next year, defined as sorted. The last step is to slice off just the first 7 entries. For this, we use the INDEX function:

  INDEX(
    sorted,
    SEQUENCE(MIN(n,ROWS(data))),
    SEQUENCE(1,COLUMNS(data))

For array, we give INDEX sorted, which is the entire list of sorted birthdays. For row_num and col_num, we use the SEQUENCE function to create the arrays we need. For row_num, we generate an array with 7 rows, or the count of all birthdays in the table, whichever is smaller:

SEQUENCE(MIN(n,ROWS(data))) // returns {1;2;3;4;5;6;7}

Notice the actual number given to SEQUENCE is either 7 or the count of all birthdays in data, whichever is smaller. This prevents errors from appearing in the final output when n is larger than the total birthdays available in the table. This little trick is handled by the MIN function, which is a nice alternative to the IF function in a situation like this:

MIN(n,ROWS(data)) // smaller of 7 or birthday count

For col_num, we create a two column array, since there are two columns in the source data:

SEQUENCE(1,COLUMNS(data)) // returns {1,2}

The final result from INDEX is the first 7 rows from the sorted birthdays. The birthdays in column F have the custom number format "mmm d" applied, to show only the month name and day.