Chapter 12: Working with Dates and Times
269
Other holidays are defined in terms of a particular occurrence of a particular week day in a particu-
lar month. For example, Labor Day falls on the first Monday in September.Figure 12.6 shows a workbook with formulas that calculate the date for 11 U.S. holidays. The for-
mulas, which reference the year in cell A1, are listed in the sections that follow.FIGURE 12.6
Using formulas to determine the date for various holidays.On the CD
The workbook shown in Figure 12.5 also appears on the companion CD-ROM. The file is named holidays.
xlsx.
New Year’s Day ..............................................................................................
This holiday always falls on January 1:=DATE(A1,1,1)Martin Luther King, Jr. Day ...........................................................................
This holiday occurs on the third Monday in January. This formula calculates Martin Luther King,
Jr. Day for the year in cell A1:=DATE(A1,1,1)+IF(2<WEEKDAY(DATE(A1,1,1)),7-WEEKDAY
(DATE(A1,1,1))+2,2-WEEKDAY(DATE(A1,1,1)))+((3-1)*7)Presidents’ Day ..............................................................................................
Presidents’ Day occurs on the third Monday in February. This formula calculates Presidents’ Day
for the year in cell A1:=DATE(A1,2,1)+IF(2<WEEKDAY(DATE(A1,2,1)),7-WEEKDAY
(DATE(A1,2,1))+2,2-WEEKDAY(DATE(A1,2,1)))+((3-1)*7)