Calculate Federal Holiday List

gunnergdr

New Member
Joined
May 21, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 15, 2002
Messages
5,028
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
May 21, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Thanks again Eric. All formulas worked in future years also! Appreciate you breaking it down
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top