Microsoft Office Tutorials and References

In Depth Information

**Determining the first day of the week after a date**

Other holidays are deﬁ ned in terms of a particular occurrence of a particular weekday in a

particular month. For example, Labor Day falls on the ﬁ rst Monday in September.

Figure 16.4 shows a workbook with formulas that calculate the date for 11 U.S. holidays.

The formulas, which reference the year in cell A1, are listed in the sections that follow.

16

FIGURE 16.4

Using formulas to determine the date for various holidays

New Year’s Day

This holiday always falls on January 1:

=DATE(A1,1,1)

Martin Luther King, Jr., Day

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)