Microsoft Office Tutorials and References

In Depth Information

**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, 2010). 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.

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:

=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)