Find the Nth Weekday in a Month in Excel

editor
March 13, 2020
Articles
0 Comments

Written by Debra Dalgleish from Contextures Blog

Some events occur on the Nth weekday of a specific month each year. Maybe your book club meets on the 4th Tuesday every month, and you don’t want to miss any meetings. In Excel, you can use a formula to find the Nth weekday in a month, and there’s an example shown below.

Nth Weekday in a Month

Aside from your book club, there are other event that always occur on the Nth weekday in a month.

For example, here in Canada, we celebrate Victoria Day on the 3rd Monday in May, and in the USA. Thanksgiving is the 4th Thursday in November.

Nth Weekday Formula

You can find the “Nth weekday in a month” dates in Excel, using a couple of Excel’s date functions – WEEKDAY and DATE.

Here’s a screen shot of my sample file, with the named ranges that I created.

calculate nth weekday in month

Here’s the formula in cell C9, to calculate the 4th Thursday in November 2020. The formula uses the defined names, instead of cell references:

=DATE(SelYr,SelMth,1
+ ((SelN-(SelWD>= WEEKDAY(DATE(SelYr,SelMth,1)))) *7)
+ (SelWD- WEEKDAY(DATE(SelYr,SelMth,1))))

NOTE: That formula is based on the late Chip Pearson’s nth day example. Chip’s site shows the formula, but doesn’t explain how it works, so I made a page on my Contextures site, with details.

How It Works

First, the Nth weekday formula calculates the 1st of the month date, with the DATE function.

DATE(SelYr,SelMth,1)

It also calculates the weekday number for that month start date, using the WEEKDAY function:

WEEKDAY(DATE(SelYr,SelMth,1)

Find the Nth Occurrence

Next, the formula checks if the 1st occurrence is in the month’s 1st week, by comparing the 1st’s weekday number, to the target weekday number.

If the 1st occurrence is in the 1st week, the formula adds (N-1) * 7 days to the start date
If the 1st occurrence is NOT in the 1st week, the formula adds (N-0) * 7 days to the start date

In November 2020, the 1st Thursday is in the 1st week, so 21 days are added, to get to the week with the Nth occurrence:

(4-1) * 7 = 21

Find the Weekday

Next, the formula adds or subtracts days, based on the weekday numbers.

If the target weekday is less than the month start weekday, days are subtracted
If the target weekday is the same as the month start weekday, zero days are added
If the target weekday is greater than the month start weekday, days are added

In November 2020, the 1st is on a Sunday (weekday 1) and the target day is a Thursday (weekday 5). The formula adds 4 days:

5 – 1 = 4

Nth Weekday Formula Result

For November 2020, the 4th Thursday is on the 26th. Here’s the formula, with the numbers used to calculate that date:

1 + ((4-1) * 7) + 4 = 26

find the nth weekday in month

Get the Sample File

For more details on the formula, and a sample file, go to the Nth Weekday page on my Contextures site.

____________________