Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to calculate dates for holidays

Date/Time Functions

How to calculate dates for holidays

by  BobJacksonNCI  Posted    (Edited  )
I wanted to automate finding the dates of holidays and came across
http://chandoo.org/wp/2009/12/24/public-holidays-excel-dates/

*** Giving credit where credit is clearly due! ***

The following is an alternative to nested IF statements which are harder to understand and explain. Purists may not like this solution because it includes the equivalent of hard-coded tables as parameters to the CHOOSE instruction.
But, it is somewhat easier to understand and explain, and is consistent.

Not included are holidays with fixed dates. E.G., Independence Day - July 4th, Veteran's Day - November 11th, Christmas, etc.

Each instruction begins with the first of the month containing the holiday desired. It then uses the day of the week of the first to make a choice (CHOOSE) from the list following. I.E., if the first of the month is Wednesday, CHOOSE picks the 4th entry from the following list because Wednesday is the fourth day of the week. (Sunday being the first day of the week is standard - adjustments are required if your setting changes the default from Sunday being the first day of the week.)

The pattern is obvious, but not so easy to explain. To prove accuracy, enough years must be tested for each instruction so all days of a week are included.

Instructions assume the year of interest is loaded into cell A1.
If you want to use the current year per your PC, replace all instances
of DATE((A1) with DATE(YEAR(TODAY())

FEDERAL HOLIDAYS:

Martin Luther King Third Monday in January
=DATE((A1),1,CHOOSE(WEEKDAY(DATE((A1),1,1)),16,15,21,20,19,18,17))

President's Day Third Monday in February
=DATE((A1),2,CHOOSE(WEEKDAY(DATE((A1),2,1)),16,15,21,20,19,18,17))

Memorial Day Last Monday in May
=DATE((A1),5,CHOOSE(WEEKDAY(DATE((A1),5,1)),30,29,28,27,26,25,31))

Labor Day First Monday in September
=DATE((A1),9,CHOOSE(WEEKDAY(DATE((A1),9,1)),2,1,7,6,5,4,3))

Columbus Day Second Monday in October
=DATE((A1),10,CHOOSE(WEEKDAY(DATE((A1),10,1)),9,8,14,13,12,11,10))

Thanksgiving Fourth Thursday in November
=DATE((A1),11,CHOOSE(WEEKDAY(DATE((A1),11,1)),26,25,24,23,22,28,27))

NON FEDERAL HOLIDAYS:

Arbor Day Last Friday in April
=DATE((A1),4,CHOOSE(WEEKDAY(DATE((A1),4,1)),27,26,25,24,30,29,28))

Mother's Day Second Sunday in May
=DATE((A1),5,CHOOSE(WEEKDAY(DATE((A1),5,1)),8,14,13,12,11,10,9))

Father's Day Third Sunday in June
=DATE((A1),6,CHOOSE(WEEKDAY(DATE((A1),6,1)),15,21,20,19,18,17,16))

Election Day Tuesday after first Monday
=DATE((A1),11,CHOOSE(WEEKDAY(DATE((A1),11,1)),2,1,7,6,5,4,3))+1

Black Friday Friday after fourth Thursday (Thanksgiving)
=DATE((A1),11,CHOOSE(WEEKDAY(DATE((A1),11,1)),26,25,24,23,22,28,27))+1

Obviously, this technique doesn't work for Easter and all holidays that are linked to Easter. (Easter is governed by ecclesiastical full moon, which isn't covered by Excel functions.) However, it can be calculated using Visual Basic code. See: http://www.cpearson.com/Excel/Easter.aspx

UPDATE - Unproven formulas to calculate Easter listed in John Walkenbach's Excel 2010 Bible. John wrote "I have no idea how they work."

Homework assignment - figure out if and how the following calculate Easter dates. :)I

Enter a year only in cell A1
=DOLLAR(("4/"&A1)/7+MOD(19*MOD(A1,19)-7,30)*14%,)*7-6
=FLOOR("5/"&DAY(MINUTE(A1/38)/2+56)&"/"&A1,7)-34
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top