0
Find Nth Weekday in MonthDiscover 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. |
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
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 ListsOn another sheet in the workbook, there are three lists, in named ranges:
|
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:
Nth Day Formula - WORKDAY.INTLThis 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
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:
Here is David's formula, which uses the WORKDAY.INTL and DATE functions:
The next section shows how this formula works.
David's formula uses the first 3 arguments of the WORKDAY.INTL function
First, for the start_date argument, the DATE function is used:
The date is calculated from the following numbers:
The day is zero, so the DATE function returns the last day of the previous month.
Next, how many working days from the start date?
For that argument, the formula refers to the "Nth" number in cell C7
For the weekend argument, the formula has a string of zeros and ones, to specify the non-working days.
In David's string, there is only one working day (zero) -- the 4th digit -- which is Thursday.
So, to calculate the Nth day's date for, we'll use Thanksgiving 2019 as an example.
In 2019, Thanksgiving falls on Thursday, November 28th.
In David's formula, he typed a 7-digit string of 1s and 0s, to specify the non-working days:
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.
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
That makes the non-working day string flexible.
The 7-digit string will change automatically, based on the day number in cell D6
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:
The calendars show those steps in 2018 and 2019.
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.
Last updated: November 8, 2022 2:21 PM