Microsoft Office Tutorials and References
In Depth Information
Date-Related Functions
Other holidays are defined in terms of a particular occurrence on a particular weekday in a
particular month. For example, Labor Day in the United States falls on the first Monday in September.
Figure 6-5 shows a workbook with formulas to calculate the date for eleven U.S. holidays. The
formulas reference the year in cell A1. Notice that because New Year’s Day, Independence Day,
Veterans Day, and Christmas Day all fall on the same days each year, their dates can be
calculated by using the simple DATE function.
Figure 6-5: Using formulas to determine the date for various holidays.
The workbook shown in Figure 6-5, holidays.xlsx , also appears on the companion
CD-ROM.
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)

Search JabSto ::

Custom Search