Part II: Working with Formulas and Functions
Calculating the date of the last weekday of the month
You can leverage the functions covered in this chapter thus far to build a dynamic date
table that automatically provides you with the last instance of a given weekday. For
instance, Figure 12.15 illustrates a table that calculates the last Sunday, Monday, Tuesday,
and so forth for each month listed.
FIGURE 12.15
A dynamic date table calculating the last weekday in each month
Cell C4 (see Figure 12.15) contains the following formula:
=DATE($A4,$B4+1,1)- WEEKDAY(DATE($A4,$B4+1,C$2))
This formula applies some basic math to calculate which date within the month should be
returned given a specific year, month, and week number.
To use the table in Figure 12.15, simply enter the years and months that you are targeting,
starting in cells A4 and B4. The idea is to use this table in your Excel models as a place to
which you can link or simply copy from to get the dates you need.
Extracting parts of a time
It’s often helpful to pick out a specific part of a time. Excel provides a simple set of func-
tions to parse time out into its component parts. These functions are as follows:
HOUR extracts the hour portion of a given time value.
MINUTE extracts the minute portion of a given time value.
SECOND extracts the second portion of a given time value.
Figure 12.16 demonstrates the use of these functions to parse the time in cell C3 into its
component parts.
