Chapter 12: Using Formulas with Dates and Times
12
Calculating the date of the Nth weekday of the month
Many analytical processes rely on knowing the dates of specific events. For example, if pay-
roll processing occurs the second Friday of every month, it’s beneficial to know which dates
in the year represent the second Friday of each month.
Using the date functions covered thus far in this chapter, you can build dynamic date
tables that automatically provide you with the key dates you need.
Figure 12.14 illustrates such a table. In this table, formulas calculate the Nth weekday for
each month listed. The idea is to fill in the years and months you need and then tell it what
number occurrence of each weekday you are seeking. In this example, cell B2 shows that
we are looking for the second occurrence of each weekday.
FIGURE 12.14
A dynamic date table calculating the Nth occurrence of each weekday
Cell C6 (see Figure 12.14) contains the following formula:
=DATE($A6,$B6,1)+C$4-WEEKDAY(DATE($A6,$B6,1))+($B$2-(C$4>=WEEKDAY(DATE
($A6,$B6,1))))*7This formula applies some basic math to calculate which date within the month should be
returned given a specific week number and occurrence.
To use the table in Figure 12.14, simply enter the years and months you are targeting,
starting in cells A6 and B6. Then adjust the occurrence number you need in cell B2.
If you are looking for the first Monday of each month, enter a 1 in cell B2 and look in the
Monday column. If you are looking for the third Thursday of each month, enter a 3 in cell
B2 and look in the Thursday column.