Find Date of Nth Weekday in Month with Excel Formula

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

Find Date of Nth Weekday in Month with Excel Formula


Original source:

Close Menu