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:

`=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...