Excel 2010 Bible

Part II: Working with Formulas and Functions

270

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. (Read about the difference between the

1900 and the 1904 date system earlier in this chapter.)

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