Microsoft Office Tutorials and References

In Depth Information

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

Easter

Calculating the date for Easter is difficult because of the complicated manner in which Easter is determined.

Easter Day is the first Sunday after the next full moon occurs after the vernal equinox. I found these formulas to

calculate Easter on the web. I have no idea how they work. And they don't work if your workbook uses the

1904 date system:

=DOLLAR((“4/”&A1)/7+MOD(19*MOD(A1,19)-7,30)*14%,)*7-6

This one is slightly shorter, but equally obtuse:

=FLOOR(“5/”&DAY(MINUTE(A1/38)/2+56)&”/”&A1,7)-34

Memorial Day

The last Monday in May is Memorial Day. This formula calculates Memorial Day for the year in cell A1:

=DATE(A1,6,1)+IF(2<WEEKDAY(DATE(A1,6,1)),7-WEEKDAY

(DATE(A1,6,1))+2,2-WEEKDAY(DATE(A1,6,1)))+((1-1)*7)-7

Notice that this formula actually calculates the first Monday in June and then subtracts 7 from the result to re-

turn the last Monday in May.

Independence Day

This holiday always falls on July 4:

=DATE(A1,7,4)

Labor Day

Labor Day occurs on the first Monday in September. This formula calculates Labor Day for the year in cell A1:

=DATE(A1,9,1)+IF(2<WEEKDAY(DATE(A1,9,1)),7-WEEKDAY

(DATE(A1,9,1))+2,2-WEEKDAY(DATE(A1,9,1)))+((1-1)*7)

Columbus Day

This holiday occurs on the second Monday in October. This formula calculates Columbus Day for the year in

cell A1:

=DATE(A1,10,1)+IF(2<WEEKDAY(DATE(A1,10,1)),7-WEEKDAY

(DATE(A1,10,1))+2,2-WEEKDAY(DATE(A1,10,1)))+((2-1)*7)

Veterans Day

This holiday always falls on November 11:

=DATE(A1,11,11)