Microsoft Office Tutorials and References
In Depth Information
Tip 37: 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:
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:
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: