# Calculate Federal Holiday List

#### gunnergdr

##### New Member
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

##### Well-known Member
Workday.xlsm
ABCDE
1HolidaysWhen202120222023
2New Year Day01-Jan-2001Fri 01-Jan-21Fri 31-Dec-21Mon 02-Jan-23
3Independence Day04-Jul-2004Mon 05-Jul-21Mon 04-Jul-22Tue 04-Jul-23
4Veterans Day11-Nov-2011Thu 11-Nov-21Fri 11-Nov-22Fri 10-Nov-23
5Christmas Day25-Dec-2020Fri 24-Dec-21Mon 26-Dec-22Mon 25-Dec-23
6
7
8
9
10HolidaysWhen TEXT202120222023
11New Year Day01-JanFri 01-Jan-21Fri 31-Dec-21Mon 02-Jan-23
12Independence Day04-JulMon 05-Jul-21Mon 04-Jul-22Tue 04-Jul-23
13Veterans Day11-NovThu 11-Nov-21Fri 11-Nov-22Fri 10-Nov-23
14Christmas Day25-DecFri 24-Dec-21Mon 26-Dec-22Mon 25-Dec-23
15
1dd
Cell Formulas
RangeFormula
C2:E5C2=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:E14C11=--(\$B11&"-"&C\$10)+CHOOSE(WEEKDAY(--(\$B11&"-"&C\$10),2),0,0,0,0,0,-1,1)

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

#### gunnergdr

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

Replies
3
Views
99
Replies
1
Views
131
Replies
5
Views
76
Replies
5
Views
985
Replies
4
Views
163

1,147,962
Messages
5,744,057
Members
423,843
Latest member
alex2022

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back