T_US_HOLIDAY_CALC

=T_US_HOLIDAY_CALC(y)

y
year, as 21 or 2021

T_US_HOLIDAY_CALC tool to calculate USA holidays.

Xlambda

Well-known Member
Joined
Mar 8, 2021
Messages
832
Office Version
  1. 365
Platform
  1. Windows
T_US_HOLIDAY_CALC tool to calculate USA holidays. If someone needs for other country, with its specific rules let me know here.
Excel Formula:
=LAMBDA(y,
    LET(f,EDATE("1-1-"&y,0),fs,f-WEEKDAY(f,3),s,SEQUENCE(54,7,fs),m,INDEX(s,,1),
       mm,MONTH(m),mlk,XLOOKUP(1,mm,m)+14,wb,XLOOKUP(2,mm,m)+14,md,XLOOKUP(5,mm,m,,,-1),ld,XLOOKUP(9,mm,m),cd,XLOOKUP(10,mm,m)+7,th,INDEX(s,,4),mth,MONTH(th),tgd,XLOOKUP(11,mth,th)+21,
      xd,DATE(YEAR(f),{1;7;11;12},{1;4;11;25}),wd,WEEKDAY(xd,3),yd,IF(wd=6,xd+1,IF(wd=5,xd-1,xd)),
      sw,SWITCH(SEQUENCE(10),1,INDEX(yd,1),2,mlk,3,wb,4,md,5,INDEX(yd,2),6,ld,7,cd,8,INDEX(yd,3),9,tgd,10,INDEX(yd,4)),
      hd,{"NYD";"MLK";"WB";"MD";"ID";"LD";"CD";"VD";"TGD";"XMAS"},
      CHOOSE({1,2},hd,sw)
    )
)
LAMBDA 5.0.xlsm
ABCDEFGHIJKLMN
1T_US_HOLIDAY_CALC
2vb.net - How to calculate holidays for the USA - Stack Overflow
3For holidays on the weekend it needs to follow policy of the US government to adjust to a weekday.
4If it falls on a Saturday it is adjusted to Friday. If it falls on a Sunday it needs to be adjusted to Monday.
5
6New Year's DayNYD01-jan202020212022
7Martin Luther King Jr.MLK3rd mon jan=T_US_HOLIDAY_CALC(2020)=T_US_HOLIDAY_CALC(21)=T_US_HOLIDAY_CALC(22)
8Washington´'s BirthdayWB3rd mon febNYD01-01-20NYD01-01-21NYD31-12-21
9Memorial DayMDlast mon mayMLK20-01-20MLK18-01-21MLK17-01-22
10Independence DayID4-julWB17-02-20WB15-02-21WB21-02-22
11Labor DayLD1st mon sepMD25-05-20MD31-05-21MD30-05-22
12Columbus DayCD2nd mon octID03-07-20ID05-07-21ID04-07-22
13Veterans dayVD11-novLD07-09-20LD06-09-21LD05-09-22
14Thanksgiving dayTGD4th thu novCD12-10-20CD11-10-21CD10-10-22
15Christmas DayXMAS25-decVD11-11-20VD11-11-21VD11-11-22
16TGD26-11-20TGD25-11-21TGD24-11-22
17XMAS25-12-20XMAS24-12-21XMAS26-12-22
18
19Full example of ACALENDAR versatilyty toghether with T_FREEDAYS and T_US_HOLIDAY_CALC embedded in a single formula
20-calculates the holidays that need specific calculating rules
21-we can select and calculate the free weekends for the whole year, no input needed.
22-we can extract working weekends exceptions
23-can add another set of dates for other important dates with different icon representation
24-no need to change the date manualy, if d,m,v are ignored,always updates to today() values
25-regular use of any calendar for any date
26
27=ACALENDAR(,"nov",,T_FREEDAYS(21,{6,7},INDEX(T_US_HOLIDAY_CALC(21),,2),L32:L33),M32:M34)
28 Nov2021
29MonTueWedThuFriSatSunExample:
30010203 ⚠04050607 ✔working
3108 ⚠091011 ✔1213 ✔14 ✔wkndsother dates
321516 ⚠171819 ?20 ✔2106-11-2108-11-21
3322232425 ✔2627 ✔28 ✔21-11-2116-11-21
34293003-11-21
35
36
T_US_HOLIDAY_CALC
Cell Formulas
RangeFormula
E7,H7,K7,D27E7=FORMULATEXT(E8)
E8:F17E8=T_US_HOLIDAY_CALC(2020)
H8:I17H8=T_US_HOLIDAY_CALC(21)
K8:L17K8=T_US_HOLIDAY_CALC(22)
D28:J35D28=ACALENDAR(,"nov",,T_FREEDAYS(21,{6,7},INDEX(T_US_HOLIDAY_CALC(21),,2),L32:L33),M32:M34)
Dynamic array formulas.
 
Last edited by a moderator:
Upvote 0
Fantastic function. I've tried to figure out the logic behind how what day is what Holiday, but it escapes me! Would love to understand the math behind it!
 
Fantastic function. I've tried to figure out the logic behind how what day is what Holiday, but it escapes me! Would love to understand the math behind it!
Thanks!
The concept is quite simple. For the entire given year, we build an array of dates, 54 rows, 7 columns. The columns represent the weekdays, Mon to Sun.
The first row of the array represents the first week of the year that contains 1st Jan of respective year.
If we need let's say the 4th Thursday of Nov, we xlookup in 4'th column first occurrence of month 11, and we add 21 days. Makes sense?
Rest is cosmetics. With today's functions everything can be made lot shorter.
Another approach on other context, more versatile, here: ADATE
Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAF
1
2yearf, first day of yearfs, fist date of sequence(the Monday before 1st jan of year)
3 =LAMBDA(y, LET( f, EDATE("1-1-" & y, 0), fs, f - WEEKDAY(f, 3), s, SEQUENCE(54, 7, fs), m, INDEX(s, , 1), mm, MONTH(m), mlk, XLOOKUP(1, mm, m) + 14, wb, XLOOKUP(2, mm, m) + 14, md, XLOOKUP(5, mm, m, , , -1), ld, XLOOKUP(9, mm, m), cd, XLOOKUP(10, mm, m) + 7, th, INDEX(s, , 4), mth, MONTH(th), tgd, XLOOKUP(11, mth, th) + 21, xd, DATE(YEAR(f), {1;7;11;12}, {1;4;11;25}), wd, WEEKDAY(xd, 3), yd, IF(wd = 6, xd + 1, IF(wd = 5, xd - 1, xd)), sw, SWITCH( SEQUENCE(10), 1, INDEX(yd, 1), 2, mlk, 3, wb, 4, md, 5, INDEX(yd, 2), 6, ld, 7, cd, 8, INDEX(yd, 3), 9, tgd, 10, INDEX(yd, 4) ), hd, {"NYD";"MLK";"WB";"MD";"ID";"LD";"CD";"VD";"TGD";"XMAS"}, CHOOSE({1,2}, hd, sw) ) )2022=EDATE("1-1-"&J3,0)=L4-WEEKDAY(L4,3)
44456244557check wkdy top left cell
5=TEXT(O4,"ddd")check
6Monm,mondays clm(1st clm)Examples:=T_US_HOLIDAY_CALC(2022)
7 <- first week (starts with Monday) of year=INDEX(L10#,,1)Columbus day, 2nd mon in octNYD44561
8s, sequence of the year by weeksmm, month of mondaysfirst mon in oct +7days=2nd mon of octMLK44578
9=SEQUENCE(54,7,O4)=MONTH(T10#)=XLOOKUP(10,V10#,T10#)+7WB44613
10→→44557445584455944560445614456244563445571244844MD44711
1144564445654456644567445684456944570445641checkID44746
1244571445724457344574445754457644577445711=TEXT(Y10,"ddd dd-mm-yy")LD44809
1344578445794458044581445824458344584445781Mon 10-10-22CD44844
1444585445864458744588445894459044591445851VD44876
1544592445934459444595445964459744598445921TGD44889
1644599446004460144602446034460444605445992XMAS44921
1744606446074460844609446104461144612446062
1844613446144461544616446174461844619446132TkGd day , 4th thu nov
1944620446214462244623446244462544626446202=XLOOKUP(11,MONTH(O10:O63),O10:O63)+21
204462744628446294463044631446324463344627344889
2144634446354463644637446384463944640446343
2244641446424464344644446454464644647446413check
2344648446494465044651446524465344654446483=TEXT(Y20,"ddd dd-mm-yy")
2444655446564465744658446594466044661446554Thu 24-11-22
2544662446634466444665446664466744668446624
2644669446704467144672446734467444675446694
2744676446774467844679446804468144682446764
2844683446844468544686446874468844689446835
2944690446914469244693446944469544696446905
3044697446984469944700447014470244703446975
3144704447054470644707447084470944710447045
3244711447124471344714447154471644717447115
3344718447194472044721447224472344724447186
3444725447264472744728447294473044731447256
3544732447334473444735447364473744738447326
3644739447404474144742447434474444745447396
3744746447474474844749447504475144752447467
3844753447544475544756447574475844759447537
3944760447614476244763447644476544766447607
4044767447684476944770447714477244773447677
4144774447754477644777447784477944780447748
4244781447824478344784447854478644787447818
4344788447894479044791447924479344794447888
4444795447964479744798447994480044801447958
4544802448034480444805448064480744808448028
4644809448104481144812448134481444815448099
4744816448174481844819448204482144822448169
4844823448244482544826448274482844829448239
4944830448314483244833448344483544836448309
50448374483844839448404484144842448434483710
51448444484544846448474484844849448504484410
52448514485244853448544485544856448574485110
53448584485944860448614486244863448644485810
54448654486644867448684486944870448714486510
55448724487344874448754487644877448784487211
56448794488044881448824488344884448854487911
57448864488744888448894489044891448924488611
58448934489444895448964489744898448994489311
59449004490144902449034490444905449064490012
60449074490844909449104491144912449134490712
61449144491544916449174491844919449204491412
62449214492244923449244492544926449274492112
6344928449294493044931449324493344934449281
64
Sheet1
Cell Formulas
RangeFormula
L3,O3,AA23,Y19,AA12,Y9,V9,L9,AD6,Q5L3=FORMULATEXT(L4)
L4L4=EDATE("1-1-"&J3,0)
O4O4=L4-WEEKDAY(L4,3)
Q6Q6=TEXT(O4,"ddd")
T7T7=FORMULATEXT(T10)
AD7:AE16AD7=T_US_HOLIDAY_CALC(2022)
L10:R63L10=SEQUENCE(54,7,O4)
T10:T63T10=INDEX(L10#,,1)
V10:V63V10=MONTH(T10#)
Y10Y10=XLOOKUP(10,V10#,T10#)+7
AA13AA13=TEXT(Y10,"ddd dd-mm-yy")
Y20Y20=XLOOKUP(11,MONTH(O10:O63),O10:O63)+21
AA24AA24=TEXT(Y20,"ddd dd-mm-yy")
Dynamic array formulas.
 
@Xlambda, I finally got time to really look at your explanation and understand it better. Lots to learn there - like in a LAMBDA a number (Year parameter) can be treated like text as in:
Excel Formula:
EDATE("1-1-"&y,0)
Anyway, I did a through analysis of the LAMBDA, and added Juneteenth to the LAMBDA:
Excel Formula:
=LAMBDA(y,
    LET(f,EDATE("1-1-"&y,0),fs,f-WEEKDAY(f,3),s,SEQUENCE(54,7,fs),m,INDEX(s,,1),
      mm,MONTH(m),mlk,XLOOKUP(1,mm,m)+14,wb,XLOOKUP(2,mm,m)+14,md,XLOOKUP(5,mm,m,,,-1),jt,XLOOKUP(6,mm,m)+14,
      ld,XLOOKUP(9,mm,m),cd,XLOOKUP(10,mm,m)+7,th,INDEX(s,,4),mth,MONTH(th),tgd,XLOOKUP(11,mth,th)+21,
      xd,DATE(YEAR(f),{1;7;11;12},{1;4;11;25}),wd,WEEKDAY(xd,3),yd,IF(wd=6,xd+1,IF(wd=5,xd-1,xd)),
      sw,SWITCH(SEQUENCE(11),1,INDEX(yd,1),2,mlk,3,wb,4,md,5,jt,6,INDEX(yd,2),7,ld,8,cd,9,INDEX(yd,3),10,tgd,11,INDEX(yd,4)),
      hd,{"NYD";"MLK";"WB";"MD";"JT";"ID";"LD";"CD";"VD";"TGD";"XMAS"},
      CHOOSE({1,2},hd,sw)
    )
)
In dissecting the formula, I came up with what might be useful for others trying to develop or understand complex LAMBDA formulas like this one. I found I was able to give the same variable names to individual or ranges of cells and deconstruct the LAMBDA step by step using the same functions and parameters in the LAMBDA itself:
Cal2.xlsx
ABCD
1VariableResultFormulaDescription
2y2023ParameterParameter
3f01/01/2023=EDATE("1-1-"&y,0)Start of calendar
4fsMonday, December 26, 2022=B3-WEEKDAY(f,3)Get First Monday to Start the calendar
5sCalendar Array=SEQUENCE(54,7,fs)Create calendar array for the year starting on the Monday before or on the 1st (fs)
6mAll Mondays Array=INDEX(s,,1)Create array of all Mondays in calendar array
7mmArray of Months of Monays Array=MONTH(m)Create array of month numbers of the Mondays array
8mlkMonday, January 16, 2023=XLOOKUP( 1, mm, m)+14XLOOKUP(1, mm, m)+14
9wbMonday, February 20, 2023=XLOOKUP( 2, mm, m)+14President's Day (Washington's Birthday)
10mdMonday, May 29, 2023=XLOOKUP( 5, mm, m,,,-1)Memorial Day
11jtMonday, June 19, 2023=XLOOKUP(6,mm,m)+14Juneteenth
12ldMonday, September 4, 2023=XLOOKUP( 9, mm, m)Labor Day
13cdMonday, October 9, 2023=XLOOKUP( 10, mm, m)+7Columbus Day
14thArray of Thursdays=INDEX( s,,4 )Array of Thursdays
15mthArray of Months of Thursdays Array=MONTH(th)Array of Months of Thursdays Array
16tgdThursday, November 23, 2023=XLOOKUP( 11, mth, th)+21Thanksgiving Day
17xdSunday, January 1, 2023=DATE( YEAR(f), {1;7;11;12}, {1;4;11;25})Array of non-Monday and Special Holidays
18xdTuesday, July 4, 2023"Array of non-Monday and Special Holidays
19xdSaturday, November 11, 2023"Array of non-Monday and Special Holidays
20xdMonday, December 25, 2023"Array of non-Monday and Special Holidays
21wd6=WEEKDAY( xd,3)Weekdays of Array of non-Monday and Special Holidays
22wd1"Weekdays of Array of non-Monday and Special Holidays
23wd5"Weekdays of Array of non-Monday and Special Holidays
24wd0"Weekdays of Array of non-Monday and Special Holidays
25ydMonday, January 2, 2023=IF( wd=6, xd+1, IF(wd=5, xd-1, xd))Adjust Array of non-Monday and Special Holidays
26ydTuesday, July 4, 2023"Adjust Array of non-Monday and Special Holidays
27ydFriday, November 10, 2023"Adjust Array of non-Monday and Special Holidays
28ydMonday, December 25, 2023"Adjust Array of non-Monday and Special Holidays
29swMonday, January 2, 2023=SWITCH(SEQUENCE(11),1,INDEX(yd,1),2,mlk,3,wb,4,md,5,jt,6,INDEX(yd,2),7,ld,8,cd,9,INDEX(yd,3),10,tgd,11,INDEX(yd,4))List of Holiday Dates
30swMonday, January 16, 2023"List of Holiday Dates
31swMonday, February 20, 2023"List of Holiday Dates
32swMonday, May 29, 2023"List of Holiday Dates
33swMonday, June 19, 2023"List of Holiday Dates
34swTuesday, July 4, 2023"List of Holiday Dates
35swMonday, September 4, 2023"List of Holiday Dates
36swMonday, October 9, 2023"List of Holiday Dates
37swFriday, November 10, 2023"List of Holiday Dates
38swThursday, November 23, 2023"List of Holiday Dates
39swMonday, December 25, 2023"List of Holiday Dates
40hdNewYear's Day={"NewYear's Day";"Martin Luther King Day";"President's Day";"Memorial Day";"Juneteenth";"Fourth of July";"Labor Day";"Columbus Day";"Veterans Day";"Thanksgiving Day";"Christmas Day"}List of Holiday Names
41hdMartin Luther King Day"List of Holiday Names
42hdPresident's Day"List of Holiday Names
43hdMemorial Day"List of Holiday Names
44hdJuneteenth"List of Holiday Names
45hdFourth of July"List of Holiday Names
46hdLabor Day"List of Holiday Names
47hdColumbus Day"List of Holiday Names
48hdVeterans Day"List of Holiday Names
49hdThanksgiving Day"List of Holiday Names
50hdChristmas Day"List of Holiday Names
51-=CHOOSE( {1,2}, hd, sw)Spilled array of Holidays starting on next row
52DONENewYear's DayMonday, January 2, 2023Spilled array of Holidays starting on next row
53DONEMartin Luther King DayMonday, January 16, 2023Spilled array of Holidays starting on next row
54DONEPresident's DayMonday, February 20, 2023Spilled array of Holidays starting on next row
55DONEMemorial DayMonday, May 29, 2023Spilled array of Holidays starting on next row
56DONEJuneteenthMonday, June 19, 2023Spilled array of Holidays starting on next row
57DONEFourth of JulyTuesday, July 4, 2023Spilled array of Holidays starting on next row
58DONELabor DayMonday, September 4, 2023Spilled array of Holidays starting on next row
59DONEColumbus DayMonday, October 9, 2023Spilled array of Holidays starting on next row
60DONEVeterans DayFriday, November 10, 2023Spilled array of Holidays starting on next row
61DONEThanksgiving DayThursday, November 23, 2023Spilled array of Holidays starting on next row
Sheet6
Cell Formulas
RangeFormula
B3B3=EDATE("1-1-"&y,0)
B4B4=B3-WEEKDAY(f,3)
B8B8=XLOOKUP( 1, mm, m)+14
B9B9=XLOOKUP( 2, mm, m)+14
B10B10=XLOOKUP( 5, mm, m,,,-1)
B11B11=XLOOKUP(6,mm,m)+14
B12B12=XLOOKUP( 9, mm, m)
B13B13=XLOOKUP( 10, mm, m)+7
C3:C4,C9:C10,C12:C13,C40,C29,C25,C21,C17C3=FORMULATEXT(B3)
C5C5=FORMULATEXT(I2)
C6C6=FORMULATEXT(Q2)
C7C7=FORMULATEXT(S2)
C8C8=FORMULATEXT(mlk)
C11C11=FORMULATEXT(jt)
C14C14=FORMULATEXT(U2)
C15C15=FORMULATEXT(W2)
B16B16=XLOOKUP( 11, mth, th)+21
C16C16=FORMULATEXT(tgd)
B17:B20B17=DATE( YEAR(f), {1;7;11;12}, {1;4;11;25})
B21:B24B21=WEEKDAY( xd,3)
B25:B28B25=IF( wd=6, xd+1, IF(wd=5, xd-1, xd))
B29:B39B29=SWITCH(SEQUENCE(11),1,INDEX(yd,1),2,mlk,3,wb,4,md,5,jt,6,INDEX(yd,2),7,ld,8,cd,9,INDEX(yd,3),10,tgd,11,INDEX(yd,4))
B40:B50B40={"NewYear's Day";"Martin Luther King Day";"President's Day";"Memorial Day";"Juneteenth";"Fourth of July";"Labor Day";"Columbus Day";"Veterans Day";"Thanksgiving Day";"Christmas Day"}
C51C51=FORMULATEXT(B52)
B52:C61B52=CHOOSE( {1,2}, hd, sw)
Dynamic array formulas.
Named Ranges
NameRefers ToCells
cd=Sheet6!$B$13C13, B29
f=Sheet6!$B$3C3, B17, B4
fs=Sheet6!$B$4C4
hd=Sheet6!$B$40:$B$49C40, B52
jt=Sheet6!$B$11C11, B29
ld=Sheet6!$B$12C12, B29
m=Sheet6!$Q$2:$Q$71C6, B8:B13
md=Sheet6!$B$10C10, B29
mlk=Sheet6!$B$8C8, B29
mm=Sheet6!$S$2:$S$71C7, B8:B13
mth=Sheet6!$W$2:$W$71C15, B16
s=Sheet6!$I$2:$O$71C5
sw=Sheet6!$B$29:$B$38C29, B52
tgd=Sheet6!$B$16C16, B29
th=Sheet6!$U$2:$U$71C14, B16
wb=Sheet6!$B$9C9, B29
wd=Sheet6!$B$21:$B$24C21, B25
xd=Sheet6!$B$17:$B$20C17, B25, B21
y=Sheet6!$B$2B3
yd=Sheet6!$B$25:$B$28C25, B29
Boy XL2BB is so cool, but wish it used the column width I had or that it could be adjusted here. There's another column with a description of what's going on.
The above uses full holiday names rather than initials - a minor, easy change that I preferred and made the process a little easier too.
Your explanation was great! Really appreciate it. Hope this helps others too!
Thanks!
.....
 
Wow, super cool, I am impressed.
For column width, before copying to mini-sheet use merge and center on your worksheet and will display ok. ✌️
 

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

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