To calculate the date of the nth weekday of the month, we need to use two Excel functions: DAY and WEEKDAY. The DAY function returns the day of the month as a number from 1 to 31, and the WEEKDAY function returns the day of the week as a number from 1 (Sunday) to 7 (Saturday).
The basic idea is to find the first day of the month, then add n times 7 days to get the start date of the nth week, then subtract the difference between the desired weekday and the weekday of the start date to get the final result.
For example, if we want to find the date of the 3rd Friday of January 2024, we can use the following steps:
- Find the first day of the month: January 1, 2024
- Add 3 times 7 days to get the start date of the 3rd week: January 1, 2024 + 21 = January 22, 2024
- Subtract the difference between the desired weekday (Friday = 6) and the weekday of the start date (Tuesday = 3): January 22, 2024 – (6 – 3) = January 19, 2024
- The final result is January 19, 2024, which is the 3rd Friday of January 2024.
Procedures
To implement the above steps in Excel formula, we can use the following generic formula:
= date - DAY ( date) + 1 + n * 7 - WEEKDAY ( date - DAY ( date) + 8 - dow)
Where:
- date is any date in the month
- n is the nth weekday of the month (from 1 to 5)
- dow is the desired weekday (from 1 to 7)
In the example above, we can use the following formula:
= B5 - DAY (B5) + 1 + D5 * 7 - WEEKDAY (B5 - DAY (B5) + 8 - C5)
Where:
- B5 is the date January 1, 2024
- D5 is the nth weekday of the month, 3
- C5 is the desired weekday, Friday = 6
The formula returns the date January 19, 2024, which is the 3rd Friday of January 2024.
Explanation
To understand how the formula works, let’s break it down into smaller parts.
Finding the first day of the month
The first part of the formula is:
= B5 - DAY (B5) + 1
This part calculates the first day of the month by subtracting the day of the month from the date, then adding 1. For example, if the date is January 15, 2024, the formula returns January 1, 2024.
Finding the start date of the nth week
The second part of the formula is:
= B5 - DAY (B5) + 1 + D5 * 7
This part adds n times 7 days to the first day of the month, to get the start date of the nth week. For example, if the date is January 1, 2024, and n is 3, the formula returns January 22, 2024.
Finding the adjustment to reach the final result
The third part of the formula is:
= WEEKDAY (B5 - DAY (B5) + 8 - C5)
This part calculates the adjustment required to reach the final result. The adjustment is the difference between the desired weekday and the weekday of the start date. To calculate this difference, we use the WEEKDAY function, which returns the day of the week as a number from 1 to 7. However, we need to make some adjustments to the arguments of the WEEKDAY function, as explained below.
The first argument of the WEEKDAY function is:
= B5 - DAY (B5) + 8
This argument is the same as the first part of the formula, except that we add 8 instead of 1. This is because we want to get the weekday of the next Sunday after the first day of the month. For example, if the date is January 1, 2024, which is a Tuesday, the formula returns January 6, 2024, which is a Sunday.
The second argument of the WEEKDAY function is:
= C5
This argument is the desired weekday, which is a number from 1 to 7. For example, if we want to find the date of the 3rd Friday of the month, the desired weekday is 6.
By subtracting the desired weekday from the first argument, we get the difference between the weekday of the next Sunday and the desired weekday. For example, if the date is January 1, 2024, and the desired weekday is Friday = 6, the formula returns 1, which is the difference between Sunday = 7 and Friday = 6.
The WEEKDAY function then returns the weekday of this difference, which is the same as the difference between the desired weekday and the weekday of the start date. For example, if the date is January 1, 2024, the start date is January 22, 2024, and the desired weekday is Friday = 6, the formula returns 3, which is the difference between Friday = 6 and Tuesday = 3.
Subtracting the adjustment from the start date
The final part of the formula is:
= B5 - DAY (B5) + 1 + D5 * 7 - WEEKDAY (B5 - DAY (B5) + 8 - C5)
This part subtracts the adjustment calculated in the previous part from the start date calculated in the second part, to get the final result. For example, if the date is January 1, 2024, the start date is January 22, 2024, the desired weekday is Friday = 6, and the adjustment is 3, the formula returns January 19, 2024, which is the 3rd Friday of January 2024.
Example
To illustrate how the formula works with real data, let’s use the following table:
| Date | nth weekday | Desired weekday | Result |
|---|---|---|---|
| 1/1/2024 | 3 | Friday | 1/19/2024 |
| 2/14/2024 | 2 | Monday | 2/12/2024 |
| 3/31/2024 | 4 | Wednesday | 3/27/2024 |
| 4/10/2024 | 1 | Sunday | 4/7/2024 |
| 5/25/2024 | 5 | Thursday | 5/30/2024 |
The formula in the Result column is:
= B5 - DAY (B5) + 1 + D5 * 7 - WEEKDAY (B5 - DAY (B5) + 8 - C5)
Where:
- B5 is the date in the Date column
- D5 is the nth weekday in the nth weekday column
- C5 is the desired weekday in the Desired weekday column
The formula returns the date of the nth weekday of the month in the Result column.
Other approaches
There are other ways to calculate the date of the nth weekday of the month in Excel formula, such as using the DATE, CHOOSE, and MOD functions. However, the formula based on the DAY and WEEKDAY functions is simpler and more flexible, as it can handle any nth weekday and any desired weekday. Therefore, I recommend using this formula for your purpose.
