Page 282 - Excel 2007 Bible

18_044039 ch13.qxp 11/21/06 11:05 AM Page 239
FIGURE 13.5
Using formulas to determine the date for various holidays.
The workbook shown in Figure 13.5 also appears on the companion CD-ROM. The file is
ON the CD-ROM
ON the CD-ROM
named holidays.xlsx.
New Year’s Day Working with Dates and Times 13
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)

Memorial Day
The last Monday in May is Memorial Day. This formula calculates Memorial Day for the year in cell A1:

=DATE(A1,6,1)+IF(2<WEEKDAY(DATE(A1,6,1)),7-WEEKDAY
(DATE(A1,6,1))+2,2-WEEKDAY(DATE(A1,6,1)))+((1-1)*7)-7
Notice that this formula actually calculates the first Monday in June and then subtracts 7 from the result to
return the last Monday in May.

239