Microsoft Office Tutorials and References
In Depth Information
Determining the first day of the week after a date
Easter
Calculating the date for Easter is difﬁ cult because of the complicated manner in which
Easter is determined. Easter Day is the ﬁ rst 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 ﬁ rst 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 ﬁ rst 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)
Search JabSto ::

Custom Search