Find Nth Weekday in Month

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.

Nth Day Formula Overview

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

There are step by step instructions below, and a sample file that you can download. This tutorial is based on Chip Pearson's nth day example.

Set Up the Worksheet

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 (SelYr)
• D5: Month (SelMth)
• D6: Weekday (SelWD)
• C7: Nth Occurrence (SelN)

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

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

In cell C9, a formula 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 two lists, in named ranges:

• DayList: Weekday names, starting with Sunday
• MonthList: Month names, starting with January

Those lists are used for the data validation drop down lists 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)

Add the Nth Day 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

 

Dates and Times FAQs