Find Nth Weekday in Month

Discover how to use Excel's date functions to find the Nth weekday in a specific month and year. For example, find the 4th Thursday in November, or the 3rd Monday in February, for any year.

Introduction

You can use Excel's date functions to find the Nth weekday in a specific month and year. For example, you might want to find the 4th Thursday in November -- the US Thanksgiving.

On the calendars below, you can see November 2018 and 2019, with the 4th Thursday highlighted in each year. These years are good examples, because they show that the 4th Thursday could occur in the 4th week, or the 5th week of November.

Set Up the Worksheets

There are two solutions below:

1. Find Nth Day with WORKDAY.INTL - Excel 2010 or later
2. Find Nth Day with WEEKDAY - any version of Excel

For both solutions, follow these steps, to set up the worksheets, and name the cells.

Setup Up Nth Day Sheet

To calculate the Nth weekday in a specific month and year, set up a worksheet with cells for the four variables that the formula needs.

The sample file uses the following cells, which have been named:

• C4: Year
• D5: Month
• D6: Weekday
• C7: Nth Occurrence

In the screen shot below, the variables are entered in the green cells, for

• Year - 2020
• Month - November
• Weekday - Thursday
• Occurrence - 4th

Later, in cell C9, you will enter a formula that calculates the date of the Nth weekday in the selected month and year

Day and Month Lists On another sheet in the workbook, there are three lists, in named ranges: MonthList: Month names, January to December DayList: Weekday names, Sunday to Saturday DayListM: Weekday names, Monday to Sunday

Those lists are used for the data validation drop down lists on the Nth Day sheet, in cells C5 and D6.

There are MATCH formulas in cells D5 and D6, to calculate the numbers for the month number and weekday number:

• D5 - Month number: =MATCH(C5,MonthList,0)
• D6 - Weekday number: =MATCH(C6,DayList,0)  (or DayListM)

Nth Day Formula - WORKDAY.INTL 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. For example, Canadian Thanksgiving is the 2nd Monday in October, so what is the date for the current year. There are written steps, and the video timeline, below the video.

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

WORKDAY.INTL Formula

This formula comes from David Newell, who posted it in a comment on my Contextures blog. Thanks, David!

David used 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

Here is David's formula, which uses the WORKDAY.INTL and DATE functions:

• =WORKDAY.INTL(DATE(C4,D5,0),C7,"1110111")

The next section shows how this formula works.

How the Formula Works

David's formula uses the first 3 arguments of the WORKDAY.INTL function

1) Start_Date

First, for the start_date argument, the DATE function is used:

• DATE(C4,D5,0)

The date is calculated from the following numbers:

• year number (C4)
• month number (D5)
• day - zero

The day is zero, so the DATE function returns the last day of the previous month.

2) Days

Next, how many working days from the start date?

For that argument, the formula refers to the "Nth" number in cell C7

3) Weekend

For the weekend argument, the formula has a string of zeros and ones, to specify the non-working days.

• String must be 7 digits, for the weekdays, from Monday to Sunday
• Use a 0 (zero) for working days
• Use a 1 for non-working days

In David's string, there is only one working day (zero) -- the 4th digit -- which is Thursday.

• "1110111"

Calculating the Nth Day's Date

So, to calculate the Nth day's date for, we'll use Thanksgiving 2019 as an example.

• The start date is October 31st -- the last day of the month before November
• Go to the 4th working day after Oct 31st
• Thursdays are the only working days, so the 4th working day after Oct 31st is Thanksgiving

In 2019, Thanksgiving falls on Thursday, November 28th.

Calculate Non-Working Day String

In David's formula, he typed a 7-digit string of 1s and 0s, to specify the non-working days:

• "1110111"

Another commenter on my blog, Hugo, noted that the 7-digit string could be calculated, instead of manually entered. He gave an example, using the CONCAT function, but that's only available in newer versions, such as Excel 365.

Instead, I modified David's formula to create the string with the REPLACE function. It's available in all versions of Excel.

• =WORKDAY.INTL(DATE(C4,D5,0),C7,REPLACE(1111111,D6,1,0))

How It Works

NOTE: For this solution, you must use DayListM, which is sorted Monday to Sunday

Here is how the REPLACE function builds the 7-digit string, with its 4 arguments

• old_text: start with a 7-digit number, 1111111
  • that represents the weekdays, Mon - Sun
  • 1 is a non-working day
• start_num: digit to change to a working day (from cell D6)
• num_chars: number of characters to replace (1)
• new_text: replace that digit with 0 (zero)
  • zero is a working day
  • for this solution, there is only one working day per week

That makes the non-working day string flexible.

The 7-digit string will change automatically, based on the day number in cell D6

Nth Day Formula - WEEKDAY

NOTE: This nth day formula is based on Chip Pearson's nth day example. It is longer than the previous solution, but will work in all versions of Excel.

To calculate the Nth weekday, we need a formula to do 3 things:

1. Find the starting date of the selected month and year
2. Find week with the Nth occurrence (4th, in this example)
3. Add or subtract days, to get to selected weekday (day 5, in this example)

The calendars show those steps in 2018 and 2019.

• In Nov 2018, the 4th Thursday is in week 4, and the same weekday
• In Nov 2019, the 4th Thursday is in week 5, and one weekday to the left

Nth Day WEEKDAY Formula

There is a long formula in cell C9, to calculate the date of the nth weekday in the selected month and year:

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

That formula does 3 things:

1. Finds the starting date of the selected month and year
2. Adds days to get to the week with the Nth occurence
3. Adds or subtracts days, to get to the selected weekday

Each part of the formula is explained below.

How the Formula Works

The parts of the formula are explained below:

1, Find Month Start Date

2. Is 1st Occurrence in 1st Week?

3. Add Days for Number of Weeks

4. Add Days for Weekday

5. Final Result of Nth Day Formula

Find Month Start Date

The first part of the formula uses the DATE function to calculate the first day of the selected month.

=DATE(SelYr,SelMth,1

The formula refers to the year and month cells on the worksheet, and uses 1 as the day number:

In the example shown below, I put this part of the formula in cell C9, to show that the month start date is November 1, 2018.

Is 1st Occurence in 1st Week?

The next part of the formula checks if the 1st occurence of the selected weekday is in the month's first week. To figure that out, the formula compares the weekday numbers of

• Month start day
• Selected weekday

Excel's WEEKDAY function calculate the weekday number for the month start date.

• WEEKDAY(DATE(SelYr,SelMth,1))

For November 2018, the first of the month is on a Thursday, which is weekay number 5

Then the formula checks if the selected weekday number is equal to or greater than the month start date's weekday number.

SelWD >= WEEKDAY(DATE(SelYr,SelMth,1))

In November 2018, that comparison is TRUE, and in November 2019, that is TRUE.

• 2018: 5 >= 5 = TRUE -- 1st instance IS week 1
• 2019: 5 >= 6 = FALSE -- 1st instance IS NOT in week 1

Add Days for Number of Weeks

Next, the formula calculates how many days to add to the month start date. The formulas uses the TRUE or FALSE from the weekday number comparison. In Excel,

• TRUE = 1
• FALSE = 0

If the 1st occurence is in the 1st week (TRUE):

• The Nth occurence is N-1 weeks down from the 1st week
• The formula adds (N-1) * 7 days to the month's start date

If the 1st occurence is NOT in the 1st week (FALSE):

• The Nth occurence is N-0 weeks down from the 1st week
• The formula adds (N-0) * 7 days to the month's start date

Add Days for Weekday

The final part of the formula adds or subtracts days, to move across to the selected weekday number.

• + (SelWD- WEEKDAY(DATE(SelYr,SelMth,1)))

The weekday number for the month start date is subtracted from the selected weekday number.

In 2018, the selected weekday is 5, and the month start date is weekday 5

• 5 - 5 = 0  (zero days added)

In 2019, the selected weekday is 5, and the month start date is weekday 6

• 5 - 6 = -1  (-1 days added)

Final Result of Nth Day Formula

Here's another look at the Nth Day of the month formula:

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

Here are the calculations for 2018 and 2019:

• 2018: 11/1/2018 + ((4-1)*7 + 0        = 11/22/2018
• 2019: 11/1/2011 + ((4-0)*7 + (-1)   = 11/28/2019

Download the Workbook

To see how this formula works, download the Nth Day of Month workbook. The zipped file is in xlsx file format, and does not contain any macros.

Related Tutorials

Functions List

Date Functions

WORKDAY.INTL function