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)

Search JabSto ::

Custom Search