T_US_HOLIDAY_CALC

[Xlambda]

T_US_HOLIDAY_CALC tool to calculate USA holidays. If someone needs for other country, with its specific rules let me know here.

=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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	T_US_HOLIDAY_CALC
2	vb.net - How to calculate holidays for the USA - Stack Overflow
3	For holidays on the weekend it needs to follow policy of the US government to adjust to a weekday.
4	If it falls on a Saturday it is adjusted to Friday. If it falls on a Sunday it needs to be adjusted to Monday.
5
6	New Year's Day	NYD	01-jan		2020			2021			2022
7	Martin Luther King Jr.	MLK	3rd mon jan		=T_US_HOLIDAY_CALC(2020)			=T_US_HOLIDAY_CALC(21)			=T_US_HOLIDAY_CALC(22)
8	Washington´'s Birthday	WB	3rd mon feb		NYD	01-01-20		NYD	01-01-21		NYD	31-12-21
9	Memorial Day	MD	last mon may		MLK	20-01-20		MLK	18-01-21		MLK	17-01-22
10	Independence Day	ID	4-jul		WB	17-02-20		WB	15-02-21		WB	21-02-22
11	Labor Day	LD	1st mon sep		MD	25-05-20		MD	31-05-21		MD	30-05-22
12	Columbus Day	CD	2nd mon oct		ID	03-07-20		ID	05-07-21		ID	04-07-22
13	Veterans day	VD	11-nov		LD	07-09-20		LD	06-09-21		LD	05-09-22
14	Thanksgiving day	TGD	4th thu nov		CD	12-10-20		CD	11-10-21		CD	10-10-22
15	Christmas Day	XMAS	25-dec		VD	11-11-20		VD	11-11-21		VD	11-11-22
16					TGD	26-11-20		TGD	25-11-21		TGD	24-11-22
17					XMAS	25-12-20		XMAS	24-12-21		XMAS	26-12-22
18
19				Full 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						Nov	2021
29				Mon	Tue	Wed	Thu	Fri	Sat	Sun		Example:
30				01	02	03 ⚠	04	05	06	07 ✔		working
31				08 ⚠	09	10	11 ✔	12	13 ✔	14 ✔		wknds	other dates
32				15	16 ⚠	17	18	19 ?	20 ✔	21		06-11-21	08-11-21
33				22	23	24	25 ✔	26	27 ✔	28 ✔		21-11-21	16-11-21
34				29	30								03-11-21
35
36
T_US_HOLIDAY_CALC

Cell Formulas
Range	Formula
E7,H7,K7,D27	E7	=FORMULATEXT(E8)
E8:F17	E8	=T_US_HOLIDAY_CALC(2020)
H8:I17	H8	=T_US_HOLIDAY_CALC(21)
K8:L17	K8	=T_US_HOLIDAY_CALC(22)
D28:J35	D28	=ACALENDAR(,"nov",,T_FREEDAYS(21,{6,7},INDEX(T_US_HOLIDAY_CALC(21),,2),L32:L33),M32:M34)
Dynamic array formulas.

 

[jdellasala]

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!

 

[Xlambda]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1
2										year		f, first day of year			fs, 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)
4												44562			44557		check wkdy top left cell
5																	=TEXT(O4,"ddd")													check
6																	Mon			m,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 oct					NYD	44561
8										↓		s, sequence of the year by weeks										mm, month of mondays			first mon in oct +7days=2nd mon of oct					MLK	44578
9										↓		=SEQUENCE(54,7,O4)										=MONTH(T10#)			=XLOOKUP(10,V10#,T10#)+7					WB	44613
10										↓	→→	44557	44558	44559	44560	44561	44562	44563		44557		12			44844					MD	44711
11												44564	44565	44566	44567	44568	44569	44570		44564		1					check			ID	44746
12												44571	44572	44573	44574	44575	44576	44577		44571		1					=TEXT(Y10,"ddd dd-mm-yy")			LD	44809
13												44578	44579	44580	44581	44582	44583	44584		44578		1					Mon 10-10-22			CD	44844
14												44585	44586	44587	44588	44589	44590	44591		44585		1								VD	44876
15												44592	44593	44594	44595	44596	44597	44598		44592		1								TGD	44889
16												44599	44600	44601	44602	44603	44604	44605		44599		2								XMAS	44921
17												44606	44607	44608	44609	44610	44611	44612		44606		2
18												44613	44614	44615	44616	44617	44618	44619		44613		2			TkGd day , 4th thu nov
19												44620	44621	44622	44623	44624	44625	44626		44620		2			=XLOOKUP(11,MONTH(O10:O63),O10:O63)+21
20												44627	44628	44629	44630	44631	44632	44633		44627		3			44889
21												44634	44635	44636	44637	44638	44639	44640		44634		3
22												44641	44642	44643	44644	44645	44646	44647		44641		3					check
23												44648	44649	44650	44651	44652	44653	44654		44648		3					=TEXT(Y20,"ddd dd-mm-yy")
24												44655	44656	44657	44658	44659	44660	44661		44655		4					Thu 24-11-22
25												44662	44663	44664	44665	44666	44667	44668		44662		4
26												44669	44670	44671	44672	44673	44674	44675		44669		4
27												44676	44677	44678	44679	44680	44681	44682		44676		4
28												44683	44684	44685	44686	44687	44688	44689		44683		5
29												44690	44691	44692	44693	44694	44695	44696		44690		5
30												44697	44698	44699	44700	44701	44702	44703		44697		5
31												44704	44705	44706	44707	44708	44709	44710		44704		5
32												44711	44712	44713	44714	44715	44716	44717		44711		5
33												44718	44719	44720	44721	44722	44723	44724		44718		6
34												44725	44726	44727	44728	44729	44730	44731		44725		6
35												44732	44733	44734	44735	44736	44737	44738		44732		6
36												44739	44740	44741	44742	44743	44744	44745		44739		6
37												44746	44747	44748	44749	44750	44751	44752		44746		7
38												44753	44754	44755	44756	44757	44758	44759		44753		7
39												44760	44761	44762	44763	44764	44765	44766		44760		7
40												44767	44768	44769	44770	44771	44772	44773		44767		7
41												44774	44775	44776	44777	44778	44779	44780		44774		8
42												44781	44782	44783	44784	44785	44786	44787		44781		8
43												44788	44789	44790	44791	44792	44793	44794		44788		8
44												44795	44796	44797	44798	44799	44800	44801		44795		8
45												44802	44803	44804	44805	44806	44807	44808		44802		8
46												44809	44810	44811	44812	44813	44814	44815		44809		9
47												44816	44817	44818	44819	44820	44821	44822		44816		9
48												44823	44824	44825	44826	44827	44828	44829		44823		9
49												44830	44831	44832	44833	44834	44835	44836		44830		9
50												44837	44838	44839	44840	44841	44842	44843		44837		10
51												44844	44845	44846	44847	44848	44849	44850		44844		10
52												44851	44852	44853	44854	44855	44856	44857		44851		10
53												44858	44859	44860	44861	44862	44863	44864		44858		10
54												44865	44866	44867	44868	44869	44870	44871		44865		10
55												44872	44873	44874	44875	44876	44877	44878		44872		11
56												44879	44880	44881	44882	44883	44884	44885		44879		11
57												44886	44887	44888	44889	44890	44891	44892		44886		11
58												44893	44894	44895	44896	44897	44898	44899		44893		11
59												44900	44901	44902	44903	44904	44905	44906		44900		12
60												44907	44908	44909	44910	44911	44912	44913		44907		12
61												44914	44915	44916	44917	44918	44919	44920		44914		12
62												44921	44922	44923	44924	44925	44926	44927		44921		12
63												44928	44929	44930	44931	44932	44933	44934		44928		1
64
Sheet1

Cell Formulas
Range	Formula
L3,O3,AA23,Y19,AA12,Y9,V9,L9,AD6,Q5	L3	=FORMULATEXT(L4)
L4	L4	=EDATE("1-1-"&J3,0)
O4	O4	=L4-WEEKDAY(L4,3)
Q6	Q6	=TEXT(O4,"ddd")
T7	T7	=FORMULATEXT(T10)
AD7:AE16	AD7	=T_US_HOLIDAY_CALC(2022)
L10:R63	L10	=SEQUENCE(54,7,O4)
T10:T63	T10	=INDEX(L10#,,1)
V10:V63	V10	=MONTH(T10#)
Y10	Y10	=XLOOKUP(10,V10#,T10#)+7
AA13	AA13	=TEXT(Y10,"ddd dd-mm-yy")
Y20	Y20	=XLOOKUP(11,MONTH(O10:O63),O10:O63)+21
AA24	AA24	=TEXT(Y20,"ddd dd-mm-yy")
Dynamic array formulas.

 

[jdellasala]

@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:

EDATE("1-1-"&y,0)

Anyway, I did a through analysis of the LAMBDA, and added Juneteenth to the LAMBDA:

=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
	A	B	C	D
1	Variable	Result	Formula	Description
2	y	2023	Parameter	Parameter
3	f	01/01/2023	=EDATE("1-1-"&y,0)	Start of calendar
4	fs	Monday, December 26, 2022	=B3-WEEKDAY(f,3)	Get First Monday to Start the calendar
5	s	Calendar Array	=SEQUENCE(54,7,fs)	Create calendar array for the year starting on the Monday before or on the 1st (fs)
6	m	All Mondays Array	=INDEX(s,,1)	Create array of all Mondays in calendar array
7	mm	Array of Months of Monays Array	=MONTH(m)	Create array of month numbers of the Mondays array
8	mlk	Monday, January 16, 2023	=XLOOKUP( 1, mm, m)+14	XLOOKUP(1, mm, m)+14
9	wb	Monday, February 20, 2023	=XLOOKUP( 2, mm, m)+14	President's Day (Washington's Birthday)
10	md	Monday, May 29, 2023	=XLOOKUP( 5, mm, m,,,-1)	Memorial Day
11	jt	Monday, June 19, 2023	=XLOOKUP(6,mm,m)+14	Juneteenth
12	ld	Monday, September 4, 2023	=XLOOKUP( 9, mm, m)	Labor Day
13	cd	Monday, October 9, 2023	=XLOOKUP( 10, mm, m)+7	Columbus Day
14	th	Array of Thursdays	=INDEX( s,,4 )	Array of Thursdays
15	mth	Array of Months of Thursdays Array	=MONTH(th)	Array of Months of Thursdays Array
16	tgd	Thursday, November 23, 2023	=XLOOKUP( 11, mth, th)+21	Thanksgiving Day
17	xd	Sunday, January 1, 2023	=DATE( YEAR(f), {1;7;11;12}, {1;4;11;25})	Array of non-Monday and Special Holidays
18	xd	Tuesday, July 4, 2023	"	Array of non-Monday and Special Holidays
19	xd	Saturday, November 11, 2023	"	Array of non-Monday and Special Holidays
20	xd	Monday, December 25, 2023	"	Array of non-Monday and Special Holidays
21	wd	6	=WEEKDAY( xd,3)	Weekdays of Array of non-Monday and Special Holidays
22	wd	1	"	Weekdays of Array of non-Monday and Special Holidays
23	wd	5	"	Weekdays of Array of non-Monday and Special Holidays
24	wd	0	"	Weekdays of Array of non-Monday and Special Holidays
25	yd	Monday, January 2, 2023	=IF( wd=6, xd+1, IF(wd=5, xd-1, xd))	Adjust Array of non-Monday and Special Holidays
26	yd	Tuesday, July 4, 2023	"	Adjust Array of non-Monday and Special Holidays
27	yd	Friday, November 10, 2023	"	Adjust Array of non-Monday and Special Holidays
28	yd	Monday, December 25, 2023	"	Adjust Array of non-Monday and Special Holidays
29	sw	Monday, 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
30	sw	Monday, January 16, 2023	"	List of Holiday Dates
31	sw	Monday, February 20, 2023	"	List of Holiday Dates
32	sw	Monday, May 29, 2023	"	List of Holiday Dates
33	sw	Monday, June 19, 2023	"	List of Holiday Dates
34	sw	Tuesday, July 4, 2023	"	List of Holiday Dates
35	sw	Monday, September 4, 2023	"	List of Holiday Dates
36	sw	Monday, October 9, 2023	"	List of Holiday Dates
37	sw	Friday, November 10, 2023	"	List of Holiday Dates
38	sw	Thursday, November 23, 2023	"	List of Holiday Dates
39	sw	Monday, December 25, 2023	"	List of Holiday Dates
40	hd	NewYear'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
41	hd	Martin Luther King Day	"	List of Holiday Names
42	hd	President's Day	"	List of Holiday Names
43	hd	Memorial Day	"	List of Holiday Names
44	hd	Juneteenth	"	List of Holiday Names
45	hd	Fourth of July	"	List of Holiday Names
46	hd	Labor Day	"	List of Holiday Names
47	hd	Columbus Day	"	List of Holiday Names
48	hd	Veterans Day	"	List of Holiday Names
49	hd	Thanksgiving Day	"	List of Holiday Names
50	hd	Christmas Day	"	List of Holiday Names
51	-		=CHOOSE( {1,2}, hd, sw)	Spilled array of Holidays starting on next row
52	DONE	NewYear's Day	Monday, January 2, 2023	Spilled array of Holidays starting on next row
53	DONE	Martin Luther King Day	Monday, January 16, 2023	Spilled array of Holidays starting on next row
54	DONE	President's Day	Monday, February 20, 2023	Spilled array of Holidays starting on next row
55	DONE	Memorial Day	Monday, May 29, 2023	Spilled array of Holidays starting on next row
56	DONE	Juneteenth	Monday, June 19, 2023	Spilled array of Holidays starting on next row
57	DONE	Fourth of July	Tuesday, July 4, 2023	Spilled array of Holidays starting on next row
58	DONE	Labor Day	Monday, September 4, 2023	Spilled array of Holidays starting on next row
59	DONE	Columbus Day	Monday, October 9, 2023	Spilled array of Holidays starting on next row
60	DONE	Veterans Day	Friday, November 10, 2023	Spilled array of Holidays starting on next row
61	DONE	Thanksgiving Day	Thursday, November 23, 2023	Spilled array of Holidays starting on next row
Sheet6

Cell Formulas
Range	Formula
B3	B3	=EDATE("1-1-"&y,0)
B4	B4	=B3-WEEKDAY(f,3)
B8	B8	=XLOOKUP( 1, mm, m)+14
B9	B9	=XLOOKUP( 2, mm, m)+14
B10	B10	=XLOOKUP( 5, mm, m,,,-1)
B11	B11	=XLOOKUP(6,mm,m)+14
B12	B12	=XLOOKUP( 9, mm, m)
B13	B13	=XLOOKUP( 10, mm, m)+7
C3:C4,C9:C10,C12:C13,C40,C29,C25,C21,C17	C3	=FORMULATEXT(B3)
C5	C5	=FORMULATEXT(I2)
C6	C6	=FORMULATEXT(Q2)
C7	C7	=FORMULATEXT(S2)
C8	C8	=FORMULATEXT(mlk)
C11	C11	=FORMULATEXT(jt)
C14	C14	=FORMULATEXT(U2)
C15	C15	=FORMULATEXT(W2)
B16	B16	=XLOOKUP( 11, mth, th)+21
C16	C16	=FORMULATEXT(tgd)
B17:B20	B17	=DATE( YEAR(f), {1;7;11;12}, {1;4;11;25})
B21:B24	B21	=WEEKDAY( xd,3)
B25:B28	B25	=IF( wd=6, xd+1, IF(wd=5, xd-1, xd))
B29:B39	B29	=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:B50	B40	={"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"}
C51	C51	=FORMULATEXT(B52)
B52:C61	B52	=CHOOSE( {1,2}, hd, sw)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
cd	=Sheet6!$B$13	C13, B29
f	=Sheet6!$B$3	C3, B17, B4
fs	=Sheet6!$B$4	C4
hd	=Sheet6!$B$40:$B$49	C40, B52
jt	=Sheet6!$B$11	C11, B29
ld	=Sheet6!$B$12	C12, B29
m	=Sheet6!$Q$2:$Q$71	C6, B8:B13
md	=Sheet6!$B$10	C10, B29
mlk	=Sheet6!$B$8	C8, B29
mm	=Sheet6!$S$2:$S$71	C7, B8:B13
mth	=Sheet6!$W$2:$W$71	C15, B16
s	=Sheet6!$I$2:$O$71	C5
sw	=Sheet6!$B$29:$B$38	C29, B52
tgd	=Sheet6!$B$16	C16, B29
th	=Sheet6!$U$2:$U$71	C14, B16
wb	=Sheet6!$B$9	C9, B29
wd	=Sheet6!$B$21:$B$24	C21, B25
xd	=Sheet6!$B$17:$B$20	C17, B25, B21
y	=Sheet6!$B$2	B3
yd	=Sheet6!$B$25:$B$28	C25, 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!
.....

 

[Xlambda]

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.