# List upcoming birthdays

To list the next n upcoming birthdays from a larger table of names and birthdays, you can use a formula based on the INDEX, XMATCH, and SORTBY functions, with help from TODAY, TEXT, SEQUENCE, and LET. In the example shown, the formula in E5 is:

where **data** (B5:C29) is an Excel Table. This formula returns the next 7 upcoming birthdays in the data based on the current date, which is November 17, 2021 in the example shown.

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

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:

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*:

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*:

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

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:

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:

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:

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:

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:

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

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.

## 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.