List nth weekdays of the month

In this example, the goal is to generate a list of "nth weekdays of the month" with a formula. For example, the formula should be able to create a list of any of the following:

• 2nd Tuesdays of the month
• 1st Fridays of the month
• 3rd Mondays of the month

This is a somewhat challenging problem in Excel, because there is no built-in function to help you find, say, the 2nd Tuesday of a given month. However, Excel offers many other powerful functions that can be used to craft a custom solution. At a high level, the approach I've taken here to solve this problem looks like this:

1. Define a start date and end date
2. Generate a list of all dates between these dates
3. Filter the list of dates by the supplied "day of week"
4. Calculate an instance number for each date
5. Filter the dates again by the desired instance number

This is a "brute force" approach, in that we don't try to do anything clever when we create our initial list of dates. Instead, we simply generate all the dates in the date range, then we come back and selectively remove dates we don't want until we are left with a final list of desired dates.

Key functions

This is a more advanced formula based on several newer functions in Excel. If you are unfamiliar with these functions, use the links below for reference:

• LET function
• SEQUENCE function
• FILTER function
• LAMBDA function
• BYROW function

Define a start and end date

The start and end date are defined in the first six lines of the formula here:

=LET(
start,EOMONTH(TODAY(),-1)+1,
months,B5,
n,B11,
dow,B8,
end,EDATE(start,months)-1,

We open with the LET function, which allows us to declare and define a number of variables. The first variable is "start", which we define to be the first day of the current month like this:

EOMONTH(TODAY(),-1)+1 // start

Moving on, we then declare and assign values to "months" (12), "n" (2), and "dow" ("Tuesday"). The abbreviation "dow" stands for "day of week". Finally, we use the EDATE function to define a value for "end":

EDATE(start,months)-1 // end

Here, we use EDATE to move 12 months forward from the start date (defined above as the 1st day in the current month), then we subtract 1 day to end up on the last day of the previous month. This gives us a date range that spans the full number of months.

At this point, we are done with set-up and have everything we need to begin generating dates.

Generate a list of all dates

Next, we use the SEQUENCE function to generate a list of all dates between start and end like this:

SEQUENCE(end-start+1,1,start,1)

This works because Excel dates are stored as large serial numbers, and SEQUENCE is designed to generate numeric arrays. The resulting array of dates is assigned to the variable "dates". For more details, see Sequence of days.

Filter the list of dates by day of week

Next, we need to filter the list of days by the "target" day of the week, previously defined as "dow" above, and given the value "Tuesday" from cell B8. To filter the list, we use the FILTER function like this:

FILTER(dates,TEXT(dates,"dddd")=dow)

Essentially, FILTER removes all dates that are not Tuesdays, by using the TEXT function to get the weekday name of each date and testing the name against the value assigned to dow ("Tuesday"). The resulting array of dates is assigned to the variable "fdates" which stands for "filtered dates".

Calculate an instance number for each date

Next, we perform the trickiest step in the formula, which is to calculate an "instance" number for each date in fdates. We do this with the BYROW function and like this:

BYROW(fdates,LAMBDA(d,SUM((TEXT(d,"mmyy")=TEXT(fdates,"mmyy"))*(d>=fdates))))

The BYROW function applies a custom LAMBDA function to each row of a given array and returns one result per row. In this case, we are using BYROW to process fdates, the array of Tuesdays defined in the previous step. Inside the LAMBDA function "d" is a variable that represents a single date in fdates. The calculation that is applied to each row looks like this:

SUM((TEXT(d,"mmyy")=TEXT(fdates,"mmyy"))*(d>=fdates))

At a high level, BYROW works through each date (d) in fdates and asks two questions:

1. Is the date (d) in the same year and month as other filtered dates (fdates)?
2. is the date (d) greater than or equal to the other dates in fdates?

The logic for the first question is based on the TEXT function here:

TEXT(d,"mmyy")=TEXT(fdates,"mmyy"))

The logic to answer the second question is here:

(d>=fdates)

Each expression results in an array of TRUE and FALSE values. The two expressions are joined by multiplication (*) which creates AND logic using Boolean algebra. The math operation automatically converts the TRUE and FALSE values to 1s and 0s, and the  SUM function sums the results. It is important to understand that this operation is performed on each date (d) in fdates. For the first Tuesday in a month, the SUM function returns 1, for the second Tuesday, SUM returns 2, and so on. The final array returned by the BYROW function looks like this:

{1;2;3;4;1;2;3;4;5;1;2;3;4;1;2;3;4;1;2;3;4;5;1;2;3;4;1;2;3;4;1;2;3;4;5;1;2;3;4;1;2;3;4;1;2;3;4;5;1;2;3;4}

In this array, each number corresponds to the "nth occurrence" of a Tuesday in a month across the full date range. The numbers reset to 1 when the month changes, so the 2's in the array represent "Second Tuesdays". The array is then assigned to the variable "instance".

Filter dates again by desired instance number

The last step in the formula is to filter the dates again by the "target" instance number like this:

FILTER(fdates,instance=n)

Here the FILTER function is configured to filter fdates. With n previously defined as 2, we have:

FILTER(fdates,instance=2)

The final result is a list of all second Tuesdays in the date range. This formula is dynamic. If the number of months (month), day of week (dow), or instance number (n) is changed, the formula will return a new set of results.

Legacy Excel

In older versions of Excel, we don't have functions like LET, FILTER, BYCOL, LAMBDA, and SEQUENCE. However, it is possible to build a list of "nth weekdays of the month" with some helper columns and a more manual approach. You can see this approach in the workbook below:

The formula in cell B11 to calculate a first-of-month date in the current month is based on the EOMONTH function and the TODAY function:

=EOMONTH(TODAY(),-1)+1

You can read more about this formula here: Get first day of month. The formula to calculate the end date in B14 is:

=EDATE(B11,B5)-1

The formula to get the first Tuesday of the month in cell D5 is interesting:

=B11+MATCH(B8,TEXT(B11+{0,1,2,3,4,5,6},"dddd"),0)-1

Basically, we need a formula that will calculate the first Tuesday after (and including) the start date. You can find a full explanation of this tricky formula here: Get next day of week. The formula in cell E5 to get the day name from the date in column D is based on the TEXT function with the custom number format "dddd".

=TEXT(D5,"dddd")

The formula in F6 to calculate "instance" is:

=IF(MONTH(D6)=MONTH(D5),F5+1,1)

Here, we use the IF function with the MONTH function to compare the month in the current row. If the month is the same, we increment by 1. If not, we reset the number to 1. As these formulas are copied down, they create a list of all the Tuesdays after the start date along with an instance number. To get a list of just the 2nd Tuesdays, use the filter to filter on rows where the instance is 2.