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:
=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
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
____________________
I have been using a slightly shorter formula some years now. Using your “variable” names…
=DATE(SelYr,SelMth,1+7*SelN)-WEEKDAY(DATE(SelYr,SelMth,8-SelWD))
That MID trick is pretty cool.
Have you considered using WORKDAY.INTL?
=WORKDAY.INTL(DATE(SelYr,SelMth,1)-1,SelN,REPLACE(“1111111″,SelWD-1,1,”0”))
This formula is 34 characters shorter (with all extra spaces removed from yours) and does not require any particularly special math to make it work.
DATE(SelYr,SelMth,1)-1
starts from the last day of the previous month
SelN
advances the desired number of workdays
REPLACE(“1111111″,SelWD-1,1,”0”)
sets the desired weekday as the only valid working day
where the -1 adjusts for the [weekend] argument using Monday as the first day of the week
So the formula for Thanksgiving 2020 would work as follows…
=WORKDAY.INTL(DATE(2020,11,1)-1,4,REPLACE(“1111111″,5-1,1,”0″))
=WORKDAY.INTL(DATE(2020,11,1)-1,4,”1110111”)
…which effectively says to advance 4 Thursdays by starting from October 31.
The formula could even be two characters shorter by using 0 as the [day] argument of the DATE function because that also results in the last day of the previous month.
=WORKDAY.INTL(DATE(SelYr,SelMth,0),SelN,REPLACE(“1111111″,SelWD-1,1,”0”))
I failed to mention that the formula would need one adjustment if the desired weekday was Monday because 1-1=0, and there is no zeroth character in a REPLACE string. So one option would be…
=WORKDAY.INTL(DATE(SelYr,SelMth,0),SelN,REPLACE(“1111111″,CHOOSE(SelWD,7,1,2,3,4,5,6),1,”0”))
…which would still be 14 characters shorter.
If you want to shorten the formula, you can replace this…
REPLACE(“1111111″,CHOOSE(SelWD,7,1,2,3,4,5,6),1,”0”)
with this…
MID(“1111101111110”,8-SelWD,7)
inside your formula.
This post reminded me of another date-based challenge from your May 21, 2019 newsletter. The following formula shows how WORKDAY.INTL could be used for that challenge and would work moving both forward and backward in time.
=WORKDAY.INTL(SelDate+SelN-SIGN(SelN),SIGN(SelN),”0000011″)
SelDate – starting date
SelN – number of days to move forward or backward (using negative for backward)