Calculate Federal Holiday List

G

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.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
Also ... hoping for an OR syntax to account for any holiday landing on Saturday (cite Friday) or Sunday (cite Monday) scenario
 
Upvote 0
Welcome to the MrExcel forum!

The principle is the same as for your Thanksgiving formula. For Christmas it would be:

Excel Formula: 
=DATE(CalendarYear,12,25)+CHOOSE(WEEKDAY(DATE(CalendarYear,12,25)),1,0,0,0,0,0,-1)

This figures out the date, then figures out the weekday of that date (1=Sun, 2=Mon, etc.), then figures out how many days to add/subtract to get to Monday or Friday. The rest can be done the same way.

This should work for any holiday with a fixed day (New Year's, July 4th, Veteran's Day, Christmas). The others that fall on a particular day of the week have different formulas.
 
Upvote 0
Thanks Eric.

That works for Christmas in 2021 as your formula suggestion did calculate to Friday, December 24, 2021.

What about the formula for Sunday, July 4, 2021? I need help calculating the formula to read Monday, July 5, 2021 in this case.

I am still seeking help for any holiday that is NOT set for Monday or Thanksgiving. Hoping to just have one set IF/OR formula to check to see if New Years, July 4, Veterans Day (Nov 11) or Christmas lands on a Saturday/Sunday and adjust accordingly (Saturday to Friday and Sunday to Monday).

Much appreciated.
 
Upvote 0
If you want to use Excel 365's Let function, review the following

Workday.xlsm
ABC
62021
7With 365 Let
8Christmas Fri 24-Dec-21Fri 24-Dec-21
9July 4Mon 05-Jul-21Mon 05-Jul-21
10Veterans DayThu 11-Nov-21Thu 11-Nov-21
11
1d
Cell Formulas
RangeFormula
B8B8=DATE(A6,12,25)+CHOOSE(WEEKDAY(DATE(A6,12,25),2),0,0,0,0,0,-1,1)
C8C8=LET(DateYr,DATE($A$6,12,25),DateYr+CHOOSE(WEEKDAY(DateYr,2),0,0,0,0,0,-1,1))
B9B9=DATE(A6,7,4)+CHOOSE(WEEKDAY(DATE(A6,7,4),2),0,0,0,0,0,-1,1)
C9C9=LET(DateYr,DATE($A$6,7,4),DateYr+CHOOSE(WEEKDAY(DateYr,2),0,0,0,0,0,-1,1))
B10B10=DATE(A6,11,11)+CHOOSE(WEEKDAY(DATE(A6,11,11),2),0,0,0,0,0,-1,1)
C10C10=LET(DateYr,DATE($A$6,11,11),DateYr+CHOOSE(WEEKDAY(DateYr,2),0,0,0,0,0,-1,1))
 
Upvote 0

gunnergdr​


Eric's formula also gives the correct result. It is a small bit more concise since it uses the default result of the Workday function.
See Excel's help for Workday.

Use the alternative that you prefer.
 
Upvote 0
Holidays.xlsx
ABCDE
1HolidaysWhen202120222023
2New Year DayJanuary 1Friday, January 1, 2021Saturday, January 1, 2022Sunday, January 1, 2023
3Martin Luther King Jr. DayThe 3rd Monday In JanuaryMonday, January 18, 2021Monday, January 17, 2022Monday, January 16, 2023
4President Day3rd Monday in FebruaryMonday, February 15, 2021Monday, February 21, 2022Monday, February 20, 2023
5Memorial DayThe last Monday in MayMonday, May 31, 2021Monday, May 30, 2022Monday, May 29, 2023
6Independence DayJuly 4Sunday, July 4, 2021Monday, July 4, 2022Tuesday, July 4, 2023
7Labor DayThe 1st Monday in SeptemberMonday, September 6, 2021Monday, September 5, 2022Monday, September 4, 2023
8Columbus DayThe 2nd Monday in OctomberMonday, October 11, 2021Monday, October 10, 2022Monday, October 9, 2023
9Veterans DayNovember 11Thursday, November 11, 2021Friday, November 11, 2022Saturday, November 11, 2023
10Thanksgiving DayThe 4th Thursday in NovemberThursday, November 25, 2021Thursday, November 24, 2022Thursday, November 23, 2023
11Christmas DayDecember 25Friday, December 24, 2021Sunday, December 25, 2022Monday, December 25, 2023
Holidays
Cell Formulas
RangeFormula
C2:E2C2=DATE(C$1,1,1)
C3:E3C3=DATE(C1,1,1)+14+CHOOSE(WEEKDAY(DATE(C1,1,1)),1,0,6,5,4,3,2)
C4:E4C4=DATE(C1,2,1)+14+CHOOSE(WEEKDAY(DATE(C1,2,1)),1,0,6,5,4,3,2)
C5:E5C5=DATE(C1,6,1)-WEEKDAY(DATE(C1,6,6))
C6:E6C6=DATE(C1,7,4)
C7:E7C7=DATE(C1,9,1)+CHOOSE(WEEKDAY(DATE(C1,9,1)),1,0,6,5,4,3,2)
C8:E8C8=DATE(C1,10,1)+7+CHOOSE(WEEKDAY(DATE(C1,10,1)),1,0,6,5,4,3,2)
C9:E9C9=DATE(C1,11,11)
C10:E10C10=DATE(C1,11,1)+21+CHOOSE(WEEKDAY(DATE(C1,11,1)),4,3,2,1,0,6,5)
C11C11=DATE(C1,12,25)+CHOOSE(WEEKDAY(DATE(C1,12,25)),1,0,0,0,0,0,-1)
D11:E11D11=DATE(D1,12,25)
 
Upvote 0
Appreciate all the help thus far and regrets for not uploading my worksheet (previous post) showing what I'm hoping to accomplish.

Given the highlighted holidays, I'm hoping to just have a formula check to see if any holiday lands on a Saturday or Sunday and adjust to Friday or Monday. I was thinking an IF/OR statement but not sure.

The idea for my sheet is to copy paste any IF/OR formula across multiple years and just have it check for the Saturday/Sunday occurrence and calculate to Friday (for Saturday) or Monday (for Sunday)

Thanks again.
 
Upvote 0
I haven't checked the other rows, but rows 2, 6, 9, and 11 should be correct:

Book1
ABCDE
1HolidaysWhen202120222023
2New Year Day1-JanFriday Jan 1,2021Friday Dec 31,2021Monday Jan 2,2023
3Martin Luther King Jr. DayThe 3rd Monday In JanuaryMonday Jan 18,2021Monday Jan 17,2022Monday Jan 16,2023
4President Day3rd Monday in FebruaryMonday Feb 15,2021Monday Feb 21,2022Monday Feb 20,2023
5Memorial DayThe last Monday in MayMonday May 31,2021Monday May 30,2022Monday May 29,2023
6Independence Day4-JulMonday Jul 5,2021Monday Jul 4,2022Tuesday Jul 4,2023
7Labor DayThe 1st Monday in SeptemberMonday Sep 6,2021Monday Sep 5,2022Monday Sep 4,2023
8Columbus DayThe 2nd Monday in OctomberMonday Oct 11,2021Monday Oct 10,2022Monday Oct 9,2023
9Veterans Day11-NovThursday Nov 11,2021Friday Nov 11,2022Friday Nov 10,2023
10Thanksgiving DayThe 4th Thursday in NovemberThursday Nov 25,2021Thursday Nov 24,2022Thursday Nov 23,2023
11Christmas Day25-DecFriday Dec 24,2021Monday Dec 26,2022Monday Dec 25,2023
Holidays
Cell Formulas
RangeFormula
C2:E2C2=DATE(C$1,1,1)+CHOOSE(WEEKDAY(DATE(C$1,1,1)),1,0,0,0,0,0,-1)
C3:E3C3=DATE(C1,1,1)+14+CHOOSE(WEEKDAY(DATE(C1,1,1)),1,0,6,5,4,3,2)
C4:E4C4=DATE(C1,2,1)+14+CHOOSE(WEEKDAY(DATE(C1,2,1)),1,0,6,5,4,3,2)
C5:E5C5=DATE(C1,6,1)-WEEKDAY(DATE(C1,6,6))
C6:E6C6=DATE(C1,7,4)+CHOOSE(WEEKDAY(DATE(C$1,7,4)),1,0,0,0,0,0,-1)
C7:E7C7=DATE(C1,9,1)+CHOOSE(WEEKDAY(DATE(C1,9,1)),1,0,6,5,4,3,2)
C8:E8C8=DATE(C1,10,1)+7+CHOOSE(WEEKDAY(DATE(C1,10,1)),1,0,6,5,4,3,2)
C9:E9C9=DATE(C1,11,11)+CHOOSE(WEEKDAY(DATE(C$1,11,11)),1,0,0,0,0,0,-1)
C10:E10C10=DATE(C1,11,1)+21+CHOOSE(WEEKDAY(DATE(C1,11,1)),4,3,2,1,0,6,5)
C11:E11C11=DATE(C1,12,25)+CHOOSE(WEEKDAY(DATE(C1,12,25)),1,0,0,0,0,0,-1)
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

Z
  • Solved
Conditional Format Date Range
Replies
4
Views
574
zero269
Z
B
  • Solved
4-10 holiday schedule formula
Replies
2
Views
433
Fluff
Fluff
R
  • Solved
Subtracting all calendar days, but end date cannot fall on a weekend or holiday. If it does, the end date shifts to the preceding work day.
Replies
8
Views
332
Romy
R
S
  • Solved
WORKDAY.INTL does not block out holidays
2
Replies
13
Views
564
shawnbull
S
R
  • Question
Calculating future date excluding Public Holiday, Weekends and Backdate specific date
Replies
1
Views
119
etaf
etaf

Forum statistics

Threads
1,215,420
Messages
6,124,803
Members
449,190
Latest member
cindykay

Share this page

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
Back
Top