Excel formulas for holidays

[BobJacksonNCI]

I wanted to automate determining when holidays occur.
Looked on the internet and found an example for Labor Day posted on

http://www.chandoo.org

illustrating a good use for the CHOOSE instruction.

Following are formulas that work for the holidays indicated
Enter the year of interest in cell D3 of a worksheet and they will work for you.



Martin Luther King 2012-01-16 =DATE((D$3),1,CHOOSE(WEEKDAY(DATE((D$3),1,1)),16,15,21,20,19,18,17))

President's Day 2012-02-20 =DATE((D$3),2,CHOOSE(WEEKDAY(DATE((D$3),2,1)),16,15,21,20,19,18,17))

Memorial Day 2012-05-28
=DATE((D$3),6,1)-WEEKDAY(DATE((D$3),6,6))

Independence Day 2012-07-04
=DATE((D$3),7,4)

Labor Day 2012-09-03 =DATE((D$3),9,CHOOSE(WEEKDAY(DATE((D$3),9,1)),2,1,7,6,5,4,3))

Columbus Day 2012-10-08 =DATE((D$3),10,CHOOSE(WEEKDAY(DATE((D$3),10,1)),9,8,14,13,12,11,10))

Thanksgiving 2012-11-22 =DATE((D$3),11,CHOOSE(WEEKDAY(DATE((D$3),11,1)),26,25,24,23,22,28,27))



HTH,
Bob

 

[SkipVought]

Bob,

You've been aroung Tek-Tips long enought to understand how things work here.

How about posting a FAQ on this, including an explanation of how to code this formula for any other holiday as well.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lionelhill]

[meandering reminiscences]
When I was a child, the standard thing to do when in church and the priest's sermon/homily was particularly loooooong, was to turn to the front pages of the old prayer book as used in the church of England, and try to calculate the date of next Easter. As I remember it involved adding to the Year of Our Lord it's Fourth Part, and further arithmetic complexities.

Excel would have spoiled the fun altogether, and meant I had to listen to the priest...
[\meandering reminiscences]