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.
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.
To calculate the Nth weekday, we need a formula to do 3 things:
The calendars show those steps in 2018 and 2019.
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.
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:
In the screen shot below, the variables are entered in the green cells, for
In cell C9, a formula calculates the date of the Nth weekday in the selected month and year
On another sheet in the workbook, there are two lists, in named ranges:
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:
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:
Each part of the formula is explained below.
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
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.
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
Excel's WEEKDAY function calculate the weekday number for the month start date.
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.
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,
If the 1st occurence is in the 1st week (TRUE):
If the 1st occurence is NOT in the 1st week (FALSE):
The final part of the formula adds or subtracts days, to move across to the selected weekday number.
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
In 2019, the selected weekday is 5, and the month start date is weekday 6
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:
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.
Don't miss my latest Excel tips and videos! Click OK, to get my weekly newsletter with Excel tips, and links to other Excel news and resources.
Last updated: March 6, 2020 3:04 PM