Is it Thanksgiving yet? Or Family Day? Or any other holiday? Use an easy formula to find out, with a couple of Excel date functions.
When Do We Get a Holiday?
Some holidays are celebrated on the same date every year, like New Year’s Day, Canada Day, and Christmas.
Other holidays are the Nth weekday in a specific month. For example, we’ll celebrate Family Day here in a few weeks – it’s on the 3rd Monday every February.
You could check a calendar, to find those holiday dates, but why not use Excel? It’s always open, right?
Video: Find Nth Weekday
This video shows how to find the Nth weekday in a specific month and year, by using the WORKDAY.INTL function in an Excel formula.
In the video, I used our Canadian Thanksgiving as the example – it falls on the 2nd Monday in October.
There’s another formula example below, and the video timeline too.
Video Timeline
- 00:00 Introduction
- 00:24 Find Nth Weekday
- 00:50 Start the Formula
- 01:53 Weekend Days
- 02:54 How It Works
- 03:15 Flexible Formula
Find Nth Weekday With Formula
I learned this short and easy Nth Weekday formula from David Newell, who shared it in a comment, here on the Contextures blog. Thanks, David!
The formula uses the WORKDAY.INTL function (Excel 2010 or later), which has these arguments:
- start_date: starting date for the calculation
- days: number of workdays before or after the start date
- weekend: (optional) days of the week that are the weekend (non-working days)
- holidays: (optional) dates that are non-working – NOT used in this formula
Find Date of Thanksgiving USA
Here’s another example of finding the Nth weekday in a month.
The American Thanksgiving is the 4th Thursday in November, so what date will that be this year?
In the screen shot below, this formula is in cell C10, to calculate the Thanksgiving date:
- =WORKDAY.INTL(DATE(C4,D5,0),C7,”1110111″)
Non-Working Day
The “magic ingredient” in the formula is the 7-digit string of non-working days – “1110111”
- string represents the 7 weekdays, from Monday to Sunday
- 0 is working, 1 is non-working
- only the 4th digit (Thursday) is a working day each week – “1110111″
So, the formula returns the date of the 4th working day in November (4th Thursday)
– November 24, 2022
Unfortunately, most of us have to work more than one day per week, and that’s why we look forward to these holidays!
Get the Workbook
Get more details on how this Nth Day formula works, and the sample workbook, on the Nth Weekday of Month page of my Contextures site.
That page also shows how to create flexible formulas, instead of hard-coding the information into the formula.
The zipped sample file is in xlsx format, and does not contain any macros.
_____________________
Find Date of Nth Weekday in Month with Excel Formula
_____________________