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)