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.
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:
+ ((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.
It also calculates the weekday number for that month start date, using the WEEKDAY function:
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
Get the Sample File
For more details on the formula, and a sample file, go to the Nth Weekday page on my Contextures site.
Find the Nth Weekday in a Month in Excel
Find the Nth Weekday in a Month in Excel is a post from Contextures Blog and is not allowed to be copied to other sites