Calculating Holidays

[jwbanks10]

I am needing to calculate holidays that I can use as part of the workday function. I used the following formula for Thanksgiving but need help understanding what it is doing so I can calculate these other holidays.

=DATE(C2,11,1)+21+CHOOSE(WEEKDAY(DATE(C2,11,1)),4,3,2,1,0,6,5) --C2 is the year

Holidays Needed

President's Day (third monday in Feb)
Memorial Day (last Monday in May)
Labor Day (first Monday in Sept)
Thanksgiving (Fourth Thursday in Nov)
Day after Thanksgiving

 

[Rick Rothstein]

I am needing to calculate holidays that I can use as part of the workday function. I used the following formula for Thanksgiving but need help understanding what it is doing so I can calculate these other holidays.

=DATE(C2,11,1)+21+CHOOSE(WEEKDAY(DATE(C2,11,1)),4,3,2,1,0,6,5) --C2 is the year

Holidays Needed

President's Day (third monday in Feb)
Memorial Day (last Monday in May)
Labor Day (first Monday in Sept)
Thanksgiving (Fourth Thursday in Nov)
Day after Thanksgiving

Here is a simpler generalize formula that will work directly for all but Memorial Day (last Monday can be either the 4th or 5th Monday of the month, so you need to tweak the formula slightly for it)...

=DATE(Yr,MM,1+7*Nth)-WEEKDAY(DATE(Yr,MM,8-DoW))

where Nth is the number you want 1st, 2nd, 3rd etc. of the month and where DoW stands for day of the week with Sunday being 1, Monday being 2, etc. Yr is the year and MM is the month number.

To get the last Monday in May, calculate the first Monday in June and subtract 7 from it.