Calculate Federal Holiday List

[gunnergdr]

I am using a formula that calculates holidays for each year so I can use it with NETWORKDAYS to exclude weekends and "OBSERVED" holidays

For example, my formula for the examples below would return Saturday, December 25, 2021 for Christmas:

New Years Day - =WORKDAY(DATE(CalendarYear,1,1),--(WEEKDAY(DATE(CalendarYear,1,1),2)>5))
Martin Luther King, Jr. Birthday - =DATE(CalendarYear,1,1)+14+CHOOSE(WEEKDAY(DATE(CalendarYear,1,1)),1,0,6,5,4,3,2)
President's Day - =DATE(CalendarYear,2,1)+14+CHOOSE(WEEKDAY(DATE(CalendarYear,2,1)),1,0,6,5,4,3,2)
Memorial Day - =DATE(CalendarYear,6,1)-WEEKDAY(DATE(CalendarYear,6,6))
Independence Day - =DATE(CalendarYear,7,4)
Labor Day - =DATE(CalendarYear,9,1)+CHOOSE(WEEKDAY(DATE(CalendarYear,9,1)),1,0,6,5,4,3,2)
Columbus Day - =DATE(CalendarYear,10,1)+7+CHOOSE(WEEKDAY(DATE(CalendarYear,10,1)),1,0,6,5,4,3,2)
Veterans Day - =DATE(CalendarYear,11,11)
Thanksgiving Day - =DATE(CalendarYear,11,1)+21+CHOOSE(WEEKDAY(DATE(CalendarYear,11,1)),4,3,2,1,0,6,5)
Christmas Day - =DATE(CalendarYear,12,25)

What I am trying to figure out is if the holiday falls on a Saturday, I need to have the formula calculate Friday as the observed holiday. However, if the holiday falls on a Sunday, I need the formula to calculate Monday as the observed holiday.

So, for Christmas in 2021, I need the formula to read Friday, December 24, 2021 for Christmas since it falls on a Saturday OR Monday, July 5, 2021 for July 4th holiday since it falls on a Sunday. For 2022, Saturday, January 1, 2022 would be observed on Friday, December 31, 2021 ... and so on.

Any help would be greatly appreciated.

 

[Dave Patton]

Workday.xlsm
	A	B	C	D	E
1	Holidays	When	2021	2022	2023
2	New Year Day	01-Jan-2001	Fri 01-Jan-21	Fri 31-Dec-21	Mon 02-Jan-23
3	Independence Day	04-Jul-2004	Mon 05-Jul-21	Mon 04-Jul-22	Tue 04-Jul-23
4	Veterans Day	11-Nov-2011	Thu 11-Nov-21	Fri 11-Nov-22	Fri 10-Nov-23
5	Christmas Day	25-Dec-2020	Fri 24-Dec-21	Mon 26-Dec-22	Mon 25-Dec-23
6
7
8
9
10	Holidays	When TEXT	2021	2022	2023
11	New Year Day	01-Jan	Fri 01-Jan-21	Fri 31-Dec-21	Mon 02-Jan-23
12	Independence Day	04-Jul	Mon 05-Jul-21	Mon 04-Jul-22	Tue 04-Jul-23
13	Veterans Day	11-Nov	Thu 11-Nov-21	Fri 11-Nov-22	Fri 10-Nov-23
14	Christmas Day	25-Dec	Fri 24-Dec-21	Mon 26-Dec-22	Mon 25-Dec-23
15
1dd

Cell Formulas
Range	Formula
C2:E5	C2	=DATE(C$1,MONTH($B2),DAY($B2))+CHOOSE(WEEKDAY(DATE(C$1,MONTH($B2),DAY($B2)),2),0,0,0,0,0,-1,1)
C11:E14	C11	=--($B11&"-"&C$10)+CHOOSE(WEEKDAY(--($B11&"-"&C$10),2),0,0,0,0,0,-1,1)

 

[gunnergdr]

Thanks again Eric. All formulas worked in future years also! Appreciate you breaking it down

 

[Eric W]

Glad we could help!