Many analytical processes rely on knowing the dates of specific events. For example, if payroll 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.
The figure 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 looking for. In this example, cell B2 shows that you are looking for the second occurrence of each weekday.
Cell C6 contains the following formula:
=DATE($A6,$B6,1)+C$4-WEEKDAY(DATE($A6,$B6,1))+($B$2-(C$4>=WEEKDAY(DATE($A6,$B6,1))))*7
This formula applies some basic math to calculate which date within the month should be returned given a specific week number and occurrence.
To use this table, simply enter the Years and Months you are targeting starting in columns A6 and B6. Then adjust the occurrence number you need in cell B2.
So, 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.