Microsoft Office Tutorials and References

In Depth Information

**Tip 37: Calculating Holidays**

Calculating Holidays

Determining the date for a particular holiday can be tricky. Some holidays, such as New Year’s Day

and Independence Day (U.S.), are no-brainers because they always occur on the same date. For these

kinds of holidays, you can simply use the DATE function. For example, to calculate New Year’s Day

(which always falls on January 1) for a specific year stored in cell A1, you can enter this function:

=DATE(A1,1,1)

Other holidays are defined in terms of a particular occurrence of a particular weekday in a particular

month. For example, Labor Day in the U.S. falls on the first Monday in September.

The formulas that follow all assume that cell A1 contains a year value (for example, 2013). Notice that

because New Year’s Day, Independence Day, Veterans Day, and Christmas Day all fall on the same

days of the year, their dates can be calculated by using the simple DATE function.

Figure 37-1 shows a workbook that contains all of these formulas.

Figure 37-1:
Formulas calculate the dates of holidays for the year in cell A1.

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. The following formula calculates Martin Luther

King Jr. Day for the year in cell A1: