ADATE

[Xlambda]

ADATE replaces only the dates column values of an array with other data types formats, leaving the rest of the array intact.
t (type) argument options : "f" , first day of month ; "e" , end of month ; "m" , month ; "y" , year ; "q" , quarter ; "od" , only date value ; "om" , only month ; "oq" , only quarter
Other functions on minisheet ADVFLT , ARRANGE , ACOMBINE

=LAMBDA(a,t,cl,
    LET(c,MAX(1,cl),l,COLUMNS(a),sl,SEQUENCE(,l),
       d,DAY(a),m,MONTH(a),y,YEAR(a),r,y&"-",
       x,SWITCH(t,"m",TEXT(a,"yyy-mm-mmm"),"y",TEXT(a,"yyy"),"e",EOMONTH(+a,0),"f",EOMONTH(+a,-1)+1,"q",IFS(m<=3,r&"Q1",
       m<=6,r&"Q2",m<=9,r&"Q3",m<=12,r&"Q4"),"od",d,"om",TEXT(a,"mm-mmm"),"oq",IFS(m<=3,"Q1",m<=6,"Q2",m<=9,"Q3",m<=12,"Q4")),
       IF(OR(t={"od","m","y","q","e","f","om","oq"}),IF(sl=c,x,a),"check data")
    )
)

LAMBDA 7.0.xlsx
	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	AG	AH	AI	AJ
1	=RANDARRAY(20,,"1-01-19",TODAY(),1)																						sample 2									filter btw Mar-2019 and Mar 2020
2	rnd dates generator		sample 1				=ADATE(C3:E22,"f",)				=ADATE(C3:E22,"e",)				=ADATE(C3:E22,"m",)				=ADATE(C3:E22,"y",)				=ARRANGE(C3:E22,{3,1})				=ADATE(W3#,"m",2)					=ADVFLT(AA3#,{2,2},{">=2019-03-mar","<=2020-03-mar"},,)
3	30-04-20		05-05-19	B	x		01-05-19	B	x		31-05-19	B	x		2019-05-May	B	x		2019	B	x		x	43590	B		x	2019-05-May	B			x	2019-05-May	B
4	01-09-19		27-02-21	A	x		01-02-21	A	x		28-02-21	A	x		2021-02-Feb	A	x		2021	A	x		x	44254	A		x	2021-02-Feb	A			y	2019-12-Dec	A
5	12-03-21		07-03-21	B	y		01-03-21	B	y		31-03-21	B	y		2021-03-Mar	B	y		2021	B	y		y	44262	B		y	2021-03-Mar	B			x	2019-05-May	B
6	30-04-21		19-12-19	A	y		01-12-19	A	y		31-12-19	A	y		2019-12-Dec	A	y		2019	A	y		y	43818	A		y	2019-12-Dec	A			y	2020-02-Feb	A
7	21-01-20		05-08-20	B	y		01-08-20	B	y		31-08-20	B	y		2020-08-Aug	B	y		2020	B	y		y	44048	B		y	2020-08-Aug	B			y	2020-02-Feb	A
8	20-07-20		08-05-19	B	x		01-05-19	B	x		31-05-19	B	x		2019-05-May	B	x		2019	B	x		x	43593	B		x	2019-05-May	B			y	2020-03-Mar	A
9	15-09-19		06-02-20	A	y		01-02-20	A	y		29-02-20	A	y		2020-02-Feb	A	y		2020	A	y		y	43867	A		y	2020-02-Feb	A			x	2019-06-Jun	A
10	21-06-20		09-02-20	A	y		01-02-20	A	y		29-02-20	A	y		2020-02-Feb	A	y		2020	A	y		y	43870	A		y	2020-02-Feb	A
11	09-05-19		09-01-19	B	x		01-01-19	B	x		31-01-19	B	x		2019-01-Jan	B	x		2019	B	x		x	43474	B		x	2019-01-Jan	B			advanced filter options are endless
12	30-08-19		24-11-20	A	x		01-11-20	A	x		30-11-20	A	x		2020-11-Nov	A	x		2020	A	x		x	44159	A		x	2020-11-Nov	A			filter all values except year 2020
13	02-09-20		28-04-20	B	y		01-04-20	B	y		30-04-20	B	y		2020-04-Apr	B	y		2020	B	y		y	43949	B		y	2020-04-Apr	B			=ADVFLT(AA3#,,,{2,2},{"<=2019-12-dec",">2020-12-dec"})
14	02-07-20		01-04-21	A	x		01-04-21	A	x		30-04-21	A	x		2021-04-Apr	A	x		2021	A	x		x	44287	A		x	2021-04-Apr	A			x	2019-05-May	B
15	02-02-20		16-04-21	B	x		01-04-21	B	x		30-04-21	B	x		2021-04-Apr	B	x		2021	B	x		x	44302	B		x	2021-04-Apr	B			x	2021-02-Feb	A
16	21-05-19		29-03-20	A	y		01-03-20	A	y		31-03-20	A	y		2020-03-Mar	A	y		2020	A	y		y	43919	A		y	2020-03-Mar	A			y	2021-03-Mar	B
17	24-02-19		16-02-21	A	y		01-02-21	A	y		28-02-21	A	y		2021-02-Feb	A	y		2021	A	y		y	44243	A		y	2021-02-Feb	A			y	2019-12-Dec	A
18	06-01-19		21-03-21	B	x		01-03-21	B	x		31-03-21	B	x		2021-03-Mar	B	x		2021	B	x		x	44276	B		x	2021-03-Mar	B			x	2019-05-May	B
19	30-08-20		18-06-19	A	x		01-06-19	A	x		30-06-19	A	x		2019-06-Jun	A	x		2019	A	x		x	43634	A		x	2019-06-Jun	A			x	2019-01-Jan	B
20	13-02-20		23-04-20	A	x		01-04-20	A	x		30-04-20	A	x		2020-04-Apr	A	x		2020	A	x		x	43944	A		x	2020-04-Apr	A			x	2021-04-Apr	A
21	21-04-20		01-01-19	B	y		01-01-19	B	y		31-01-19	B	y		2019-01-Jan	B	y		2019	B	y		y	43466	B		y	2019-01-Jan	B			x	2021-04-Apr	B
22	28-07-19		08-10-20	A	y		01-10-20	A	y		31-10-20	A	y		2020-10-Oct	A	y		2020	A	y		y	44112	A		y	2020-10-Oct	A			y	2021-02-Feb	A
23																																x	2021-03-Mar	B
24							=ADATE(C3:E22,"q",)				=ADATE(C3:E22,"od",)				=ADATE(C3:E22,"om",)				=ADATE(C3:E22,"oq",)				=ADATE(W3#,"oq",2)				filter only Q1 or Q4 values all time					x	2019-06-Jun	A
25	t (type) argument		data type	format			2019-Q2	B	x		5	B	x		05-May	B	x		Q2	B	x		x	Q2	B		=ADVFLT(W25#,,,{2,2},{"q1","q4"})					y	2019-01-Jan	B
26	f	first of month	date				2021-Q1	A	x		27	A	x		02-Feb	A	x		Q1	A	x		x	Q1	A		x	Q1	A
27	e	end of month	date				2021-Q1	B	y		7	B	y		03-Mar	B	y		Q1	B	y		y	Q1	B		y	Q1	B
28	m	month	text	yyyy-mm-mmm			2019-Q4	A	y		19	A	y		12-Dec	A	y		Q4	A	y		y	Q4	A		y	Q4	A
29	y	year	numeric				2020-Q3	B	y		5	B	y		08-Aug	B	y		Q3	B	y		y	Q3	B		y	Q1	A
30	q	quarter	text	yyyy-Qn			2019-Q2	B	x		8	B	x		05-May	B	x		Q2	B	x		x	Q2	B		y	Q1	A
31	od	only date	numeric				2020-Q1	A	y		6	A	y		02-Feb	A	y		Q1	A	y		y	Q1	A		x	Q1	B
32	om	only month	text	dd-mmm			2020-Q1	A	y		9	A	y		02-Feb	A	y		Q1	A	y		y	Q1	A		x	Q4	A
33	oq	only quarter	text	Qn			2019-Q1	B	x		9	B	x		01-Jan	B	x		Q1	B	x		x	Q1	B		y	Q1	A
34							2020-Q4	A	x		24	A	x		11-Nov	A	x		Q4	A	x		x	Q4	A		y	Q1	A
35	Note:ADVFLT criteria arguments should follow						2020-Q2	B	y		28	B	y		04-Apr	B	y		Q2	B	y		y	Q2	B		x	Q1	B
36	the t (type) argument format of ADATE(see table)						2021-Q2	A	x		1	A	x		04-Apr	A	x		Q2	A	x		x	Q2	A		y	Q1	B
37							2021-Q2	B	x		16	B	x		04-Apr	B	x		Q2	B	x		x	Q2	B		y	Q4	A
38	other functions on minisheet						2020-Q1	A	y		29	A	y		03-Mar	A	y		Q1	A	y		y	Q1	A
39		ADVFLT					2021-Q1	A	y		16	A	y		02-Feb	A	y		Q1	A	y		y	Q1	A
40		ACOMBINE					2021-Q1	B	x		21	B	x		03-Mar	B	x		Q1	B	x		x	Q1	B		list all possible combinations
41		ARRANGE					2019-Q2	A	x		18	A	x		06-Jun	A	x		Q2	A	x		x	Q2	A		=ACOMBINE(AA26#,{2,3,1})
42							2020-Q2	A	x		23	A	x		04-Apr	A	x		Q2	A	x		x	Q2	A		Q1	A	x
43							2019-Q1	B	y		1	B	y		01-Jan	B	y		Q1	B	y		y	Q1	B		Q1	A	y
44							2020-Q4	A	y		8	A	y		10-Oct	A	y		Q4	A	y		y	Q4	A		Q1	B	x
45																											Q1	B	y
46																											Q4	A	x
47																											Q4	A	y
48																											Q4	B	x
49																											Q4	B	y
50
ADATE post

Cell Formulas
Range	Formula
A1	A1	=FORMULATEXT(A3)
G2,K2,O2,AA41,AA25,G24,K24,O24,S24,W24,AF13,S2,W2,AA2,AF2	G2	=FORMULATEXT(G3)
A3:A22	A3	=RANDARRAY(20,,"1-01-19",TODAY(),1)
G3:I22	G3	=ADATE(C3:E22,"f",)
K3:M22	K3	=ADATE(C3:E22,"e",)
O3:Q22	O3	=ADATE(C3:E22,"m",)
S3:U22	S3	=ADATE(C3:E22,"y",)
W3:Y22	W3	=ARRANGE(C3:E22,{3,1})
AA3:AC22	AA3	=ADATE(W3#,"m",2)
AF3:AH9	AF3	=ADVFLT(AA3#,{2,2},{">=2019-03-mar","<=2020-03-mar"},,)
AF14:AH25	AF14	=ADVFLT(AA3#,,,{2,2},{"<=2019-12-dec",">2020-12-dec"})
G25:I44	G25	=ADATE(C3:E22,"q",)
K25:M44	K25	=ADATE(C3:E22,"od",)
O25:Q44	O25	=ADATE(C3:E22,"om",)
S25:U44	S25	=ADATE(C3:E22,"oq",)
W25:Y44	W25	=ADATE(W3#,"oq",2)
AA26:AC37	AA26	=ADVFLT(W25#,,,{2,2},{"q1","q4"})
AA42:AC49	AA42	=ACOMBINE(AA26#,{2,3,1})
Dynamic array formulas.

 

[Xlambda]

Just added new type argument functionality for weekdays only "ow" , (only weekdays) . An example will follow.
Format is "[wkday index] ddd" , with Monday being first day of the week (like 1 Mon , 2 Tue...) To change it to 1 Sun, 2 Mon...we have to change only 1 digit of wx variable from 2 to 1 (LET row)
....wx,XMATCH(w,TEXT(SEQUENCE(7,,2),"ddd")).....to ...wx,XMATCH(w,TEXT(SEQUENCE(7,,1),"ddd"))....
Other functions on minisheet APIVOT . This is the modified lambda:

=LAMBDA(ar,t,cl,
    LET(c,MAX(1,cl),l,COLUMNS(ar),sl,SEQUENCE(,l),a,INDEX(ar,,c),d,DAY(a),m,MONTH(a),y,YEAR(a),w,TEXT(a,"ddd"),wx,XMATCH(w,TEXT(SEQUENCE(7,,2),"ddd")),
       r,y&"-",x,SWITCH(t,"m",TEXT(a,"yyy-mm-mmm"),"y",y,"e",EOMONTH(+a,0),"f",EOMONTH(+a,-1)+1,"q",IFS(m<=3,r&"Q1",m<=6,r&"Q2",m<=9,r&"Q3",m<=12,r&"Q4"),
         "od",d,"om",TEXT(a,"mm-mmm"),"oq",IFS(m<=3,"Q1",m<=6,"Q2",m<=9,"Q3",m<=12,"Q4"),"ow",wx&" "&w),IF(OR(t={"od","m","y","q","e","f","om","oq","ow"}),
       IF(sl=c,x,ar),"check data")
    )
)

LAMBDA 7.0.xlsx
	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	AG
1	Challenge: Weekdays all time ranking of Revenues,Profit,Units sold values for each product,top to bottom(descending order)																										highest values,top
2																											lowest values, bottom
3	Date	Name	Prod.	Cost	U.Prc	U.Sold	Rev	Profit		=ADATE(A4:H53,"ow",)									PT revenue(wkday\product)
4	24-04-21	Antione Grinnell	Sunset	9	13	6	78	24		6 Sat	Antione Grinnell	Sunset	9	13	6	78	24		(1\3) 7 vf=0	Crested	Majestic	Quad	Sunset	Yanaki	Grand Total		Crested	Majestic	Quad	Sunset	Yanaki	Grand Total
5	25-12-20	Freddie Otten	Majestic	8	12	13	156	52		5 Fri	Freddie Otten	Majestic	8	12	13	156	52		1 Mon	70	276	0	403	0	749		2 Tue	2 Tue	5 Fri	1 Mon	2 Tue	2 Tue
6	14-05-20	Freddie Otten	Quad	7	10	12	120	36		4 Thu	Freddie Otten	Quad	7	10	12	120	36		2 Tue	364	432	360	78	594	1828		7 Sun	1 Mon	7 Sun	4 Thu	7 Sun	7 Sun
7	23-05-21	Jesus Pasquariello	Sunset	9	13	11	143	44		7 Sun	Jesus Pasquariello	Sunset	9	13	11	143	44		3 Wed	0	156	120	0	0	276		5 Fri	3 Wed	2 Tue	7 Sun	6 Sat	5 Fri
8	22-12-20	Antione Grinnell	Sunset	9	13	6	78	24		2 Tue	Antione Grinnell	Sunset	9	13	6	78	24		4 Thu	0	0	120	351	0	471		1 Mon	5 Fri	6 Sat	2 Tue	5 Fri	1 Mon
9	19-03-21	Antione Grinnell	Crested	5	14	15	210	135		5 Fri	Antione Grinnell	Crested	5	14	15	210	135		5 Fri	210	156	460	0	55	881		3 Wed	7 Sun	3 Wed	6 Sat	1 Mon	4 Thu
10	21-09-20	Freddie Otten	Sunset	9	13	5	65	20		1 Mon	Freddie Otten	Sunset	9	13	5	65	20		6 Sat	0	0	150	78	154	382		4 Thu	4 Thu	4 Thu	3 Wed	3 Wed	6 Sat
11	31-08-20	Rey Mcglothlin	Majestic	8	12	8	96	32		1 Mon	Rey Mcglothlin	Majestic	8	12	8	96	32		7 Sun	238	108	460	325	165	1296		6 Sat	6 Sat	1 Mon	5 Fri	4 Thu	3 Wed
12	10-11-20	Theodore Kriger	Majestic	8	12	12	144	48		2 Tue	Theodore Kriger	Majestic	8	12	12	144	48		Grand Total	882	1128	1670	1235	968	5883		=SORTBY($S$5:$S$11,T5:T11,-1)
13	21-09-20	Antione Grinnell	Majestic	8	12	6	72	24		1 Mon	Antione Grinnell	Majestic	8	12	6	72	24		=APIVOT(J4#,1,3,7,)
14	25-12-20	Houston Ybarbo	Quad	7	10	13	130	39		5 Fri	Houston Ybarbo	Quad	7	10	13	130	39
15	11-05-21	Rey Mcglothlin	Yanaki	6	11	12	132	60		2 Tue	Rey Mcglothlin	Yanaki	6	11	12	132	60		PT profit(wkday\product)
16	28-05-21	Antione Grinnell	Quad	7	10	9	90	27		5 Fri	Antione Grinnell	Quad	7	10	9	90	27		(1\3) 8 vf=0	Crested	Majestic	Quad	Sunset	Yanaki	Grand Total		Crested	Majestic	Quad	Sunset	Yanaki	Grand Total
17	30-05-21	Theodore Kriger	Quad	7	10	5	50	15		7 Sun	Theodore Kriger	Quad	7	10	5	50	15		1 Mon	45	92	0	124	0	261		2 Tue	2 Tue	5 Fri	1 Mon	2 Tue	2 Tue
18	15-09-20	Rey Mcglothlin	Crested	5	14	12	168	108		2 Tue	Rey Mcglothlin	Crested	5	14	12	168	108		2 Tue	234	144	108	24	270	780		7 Sun	1 Mon	7 Sun	4 Thu	7 Sun	7 Sun
19	24-01-21	Freddie Otten	Yanaki	6	11	6	66	30		7 Sun	Freddie Otten	Yanaki	6	11	6	66	30		3 Wed	0	52	36	0	0	88		5 Fri	3 Wed	2 Tue	7 Sun	6 Sat	5 Fri
20	16-03-21	Mel Scheidler	Majestic	8	12	6	72	24		2 Tue	Mel Scheidler	Majestic	8	12	6	72	24		4 Thu	0	0	36	108	0	144		1 Mon	5 Fri	6 Sat	2 Tue	5 Fri	1 Mon
21	24-01-21	Jesus Pasquariello	Crested	5	14	12	168	108		7 Sun	Jesus Pasquariello	Crested	5	14	12	168	108		5 Fri	135	52	138	0	25	350		3 Wed	7 Sun	3 Wed	6 Sat	1 Mon	4 Thu
22	07-12-20	Theodore Kriger	Sunset	9	13	13	169	52		1 Mon	Theodore Kriger	Sunset	9	13	13	169	52		6 Sat	0	0	45	24	70	139		4 Thu	4 Thu	4 Thu	3 Wed	3 Wed	6 Sat
23	24-04-21	Houston Ybarbo	Yanaki	6	11	14	154	70		6 Sat	Houston Ybarbo	Yanaki	6	11	14	154	70		7 Sun	153	36	138	100	75	502		6 Sat	6 Sat	1 Mon	5 Fri	4 Thu	3 Wed
24	19-03-21	Rey Mcglothlin	Quad	7	10	7	70	21		5 Fri	Rey Mcglothlin	Quad	7	10	7	70	21		Grand Total	567	376	501	380	440	2264		=SORTBY($S$17:$S$23,T17:T23,-1)
25	06-09-20	Antione Grinnell	Quad	7	10	15	150	45		7 Sun	Antione Grinnell	Quad	7	10	15	150	45		=APIVOT(J4#,1,3,8,)
26	10-03-21	Mel Scheidler	Quad	7	10	12	120	36		3 Wed	Mel Scheidler	Quad	7	10	12	120	36
27	14-10-20	Jesus Pasquariello	Majestic	8	12	13	156	52		3 Wed	Jesus Pasquariello	Majestic	8	12	13	156	52		PT units sold(wkday\product)
28	08-09-20	Theodore Kriger	Quad	7	10	15	150	45		2 Tue	Theodore Kriger	Quad	7	10	15	150	45		(1\3) 6 vf=0	Crested	Majestic	Quad	Sunset	Yanaki	Grand Total		Crested	Majestic	Quad	Sunset	Yanaki	Grand Total
29	23-06-20	Antione Scheidler	Yanaki	6	11	15	165	75		2 Tue	Antione Scheidler	Yanaki	6	11	15	165	75		1 Mon	5	23	0	31	0	59		2 Tue	2 Tue	5 Fri	1 Mon	2 Tue	2 Tue
30	07-06-20	Theodore Kriger	Sunset	9	13	14	182	56		7 Sun	Theodore Kriger	Sunset	9	13	14	182	56		2 Tue	26	36	36	6	54	158		7 Sun	1 Mon	7 Sun	4 Thu	7 Sun	7 Sun
31	27-04-21	Antione Scheidler	Crested	5	14	7	98	63		2 Tue	Antione Scheidler	Crested	5	14	7	98	63		3 Wed	0	13	12	0	0	25		5 Fri	3 Wed	2 Tue	7 Sun	6 Sat	5 Fri
32	11-05-21	Rey Mcglothlin	Quad	7	10	15	150	45		2 Tue	Rey Mcglothlin	Quad	7	10	15	150	45		4 Thu	0	0	12	27	0	39		1 Mon	5 Fri	6 Sat	2 Tue	5 Fri	1 Mon
33	07-12-20	Houston Ybarbo	Crested	5	14	5	70	45		1 Mon	Houston Ybarbo	Crested	5	14	5	70	45		5 Fri	15	13	46	0	5	79		3 Wed	7 Sun	3 Wed	6 Sat	1 Mon	4 Thu
34	11-03-21	Rey Mcglothlin	Sunset	9	13	12	156	48		4 Thu	Rey Mcglothlin	Sunset	9	13	12	156	48		6 Sat	0	0	15	6	14	35		4 Thu	4 Thu	4 Thu	3 Wed	3 Wed	6 Sat
35	24-01-21	Mel Scheidler	Crested	5	14	5	70	45		7 Sun	Mel Scheidler	Crested	5	14	5	70	45		7 Sun	17	9	46	25	15	112		6 Sat	6 Sat	1 Mon	5 Fri	4 Thu	3 Wed
36	02-04-21	Jesus Pasquariello	Quad	7	10	9	90	27		5 Fri	Jesus Pasquariello	Quad	7	10	9	90	27		Grand Total	63	94	167	95	88	507		=SORTBY($S$29:$S$35,T29:T35,-1)
37	04-05-21	Rey Mcglothlin	Yanaki	6	11	6	66	30		2 Tue	Rey Mcglothlin	Yanaki	6	11	6	66	30		=APIVOT(J4#,1,3,6,)
38	10-11-20	Houston Ybarbo	Yanaki	6	11	12	132	60		2 Tue	Houston Ybarbo	Yanaki	6	11	12	132	60
39	28-11-20	Mel Scheidler	Quad	7	10	15	150	45		6 Sat	Mel Scheidler	Quad	7	10	15	150	45
40	03-09-20	Jesus Pasquariello	Sunset	9	13	15	195	60		4 Thu	Jesus Pasquariello	Sunset	9	13	15	195	60
41	02-04-21	Rey Mcglothlin	Yanaki	6	11	5	55	25		5 Fri	Rey Mcglothlin	Yanaki	6	11	5	55	25
42	20-12-20	Freddie Otten	Majestic	8	12	9	108	36		7 Sun	Freddie Otten	Majestic	8	12	9	108	36
43	21-09-20	Houston Ybarbo	Sunset	9	13	13	169	52		1 Mon	Houston Ybarbo	Sunset	9	13	13	169	52
44	04-08-20	Freddie Otten	Yanaki	6	11	9	99	45		2 Tue	Freddie Otten	Yanaki	6	11	9	99	45
45	13-04-21	Mel Scheidler	Majestic	8	12	9	108	36		2 Tue	Mel Scheidler	Majestic	8	12	9	108	36
46	11-05-21	Antione Scheidler	Quad	7	10	6	60	18		2 Tue	Antione Scheidler	Quad	7	10	6	60	18
47	25-12-20	Theodore Kriger	Quad	7	10	8	80	24		5 Fri	Theodore Kriger	Quad	7	10	8	80	24
48	31-08-20	Houston Ybarbo	Majestic	8	12	9	108	36		1 Mon	Houston Ybarbo	Majestic	8	12	9	108	36
49	23-03-21	Freddie Otten	Majestic	8	12	9	108	36		2 Tue	Freddie Otten	Majestic	8	12	9	108	36
50	20-12-20	Mel Scheidler	Quad	7	10	11	110	33		7 Sun	Mel Scheidler	Quad	7	10	11	110	33
51	22-12-20	Antione Grinnell	Crested	5	14	7	98	63		2 Tue	Antione Grinnell	Crested	5	14	7	98	63
52	30-05-21	Freddie Otten	Quad	7	10	15	150	45		7 Sun	Freddie Otten	Quad	7	10	15	150	45
53	30-05-21	Antione Grinnell	Yanaki	6	11	9	99	45		7 Sun	Antione Grinnell	Yanaki	6	11	9	99	45
54
ADATE only wkday

Cell Formulas
Range	Formula
J3	J3	=FORMULATEXT(J4)
G4:G53	G4	=E4:E53*F4:F53
H4:H53	H4	=G4#-(D4:D53*F4:F53)
J4:Q53	J4	=ADATE(A4:H53,"ow",)
S4:Y12	S4	=APIVOT(J4#,1,3,7,)
AA5:AF11	AA5	=SORTBY($S$5:$S$11,T5:T11,-1)
AA12,AA36,AA24	AA12	=FORMULATEXT(AA5)
S13,S37,S25	S13	=FORMULATEXT(S4)
S16:Y24	S16	=APIVOT(J4#,1,3,8,)
AA17:AF23	AA17	=SORTBY($S$17:$S$23,T17:T23,-1)
S28:Y36	S28	=APIVOT(J4#,1,3,6,)
AA29:AF35	AA29	=SORTBY($S$29:$S$35,T29:T35,-1)
Dynamic array formulas.

 

[Xlambda]

Survey of all type arguments

LAMBDA 7.0.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1							=ADATE($F$3#,G2,)
2	=RANDARRAY(40,,"1-jan-18",TODAY(),1)					type array	f	e	m	y	q	od	om	oq	ow
3						28-Feb-18	01-02-18	28-02-18	2018-02-Feb	2018	2018-Q1	28	02-Feb	Q1	3 Wed
4	ADATE(ar,t,cl)					01-Sep-18	01-09-18	30-09-18	2018-09-Sep	2018	2018-Q3	1	09-Sep	Q3	6 Sat
5	ar: array					08-May-21	01-05-21	31-05-21	2021-05-May	2021	2021-Q2	8	05-May	Q2	6 Sat
6	t: type					15-Jun-19	01-06-19	30-06-19	2019-06-Jun	2019	2019-Q2	15	06-Jun	Q2	6 Sat
7	cl: date column index					05-May-18	01-05-18	31-05-18	2018-05-May	2018	2018-Q2	5	05-May	Q2	6 Sat
8						13-Oct-18	01-10-18	31-10-18	2018-10-Oct	2018	2018-Q4	13	10-Oct	Q4	6 Sat
9	t (type) argument		data type	format		14-Aug-19	01-08-19	31-08-19	2019-08-Aug	2019	2019-Q3	14	08-Aug	Q3	3 Wed
10	f	first of month	date			19-Feb-18	01-02-18	28-02-18	2018-02-Feb	2018	2018-Q1	19	02-Feb	Q1	1 Mon
11	e	end of month	date			03-Oct-19	01-10-19	31-10-19	2019-10-Oct	2019	2019-Q4	3	10-Oct	Q4	4 Thu
12	m	month	text	yyyy-mm-mmm		06-Jan-20	01-01-20	31-01-20	2020-01-Jan	2020	2020-Q1	6	01-Jan	Q1	1 Mon
13	y	year	numeric			11-May-20	01-05-20	31-05-20	2020-05-May	2020	2020-Q2	11	05-May	Q2	1 Mon
14	q	quarter	text	yyyy-Qn		09-Aug-18	01-08-18	31-08-18	2018-08-Aug	2018	2018-Q3	9	08-Aug	Q3	4 Thu
15	od	only date	numeric			21-Nov-19	01-11-19	30-11-19	2019-11-Nov	2019	2019-Q4	21	11-Nov	Q4	4 Thu
16	om	only month	text	mm-mmm		16-Oct-20	01-10-20	31-10-20	2020-10-Oct	2020	2020-Q4	16	10-Oct	Q4	5 Fri
17	oq	only quarter	text	Qn		14-Sep-20	01-09-20	30-09-20	2020-09-Sep	2020	2020-Q3	14	09-Sep	Q3	1 Mon
18	ow	only weekday	text	[wkday index] ddd		27-May-20	01-05-20	31-05-20	2020-05-May	2020	2020-Q2	27	05-May	Q2	3 Wed
19						22-Mar-18	01-03-18	31-03-18	2018-03-Mar	2018	2018-Q1	22	03-Mar	Q1	4 Thu
20						04-Feb-19	01-02-19	28-02-19	2019-02-Feb	2019	2019-Q1	4	02-Feb	Q1	1 Mon
21						16-Jun-18	01-06-18	30-06-18	2018-06-Jun	2018	2018-Q2	16	06-Jun	Q2	6 Sat
22						30-Jun-18	01-06-18	30-06-18	2018-06-Jun	2018	2018-Q2	30	06-Jun	Q2	6 Sat
23						04-May-21	01-05-21	31-05-21	2021-05-May	2021	2021-Q2	4	05-May	Q2	2 Tue
24						14-Apr-19	01-04-19	30-04-19	2019-04-Apr	2019	2019-Q2	14	04-Apr	Q2	7 Sun
25						29-Nov-18	01-11-18	30-11-18	2018-11-Nov	2018	2018-Q4	29	11-Nov	Q4	4 Thu
26						05-Apr-19	01-04-19	30-04-19	2019-04-Apr	2019	2019-Q2	5	04-Apr	Q2	5 Fri
27						01-Aug-20	01-08-20	31-08-20	2020-08-Aug	2020	2020-Q3	1	08-Aug	Q3	6 Sat
28						04-Aug-20	01-08-20	31-08-20	2020-08-Aug	2020	2020-Q3	4	08-Aug	Q3	2 Tue
29						30-Aug-18	01-08-18	31-08-18	2018-08-Aug	2018	2018-Q3	30	08-Aug	Q3	4 Thu
30						07-Jan-18	01-01-18	31-01-18	2018-01-Jan	2018	2018-Q1	7	01-Jan	Q1	7 Sun
31						03-May-20	01-05-20	31-05-20	2020-05-May	2020	2020-Q2	3	05-May	Q2	7 Sun
32						27-Jan-19	01-01-19	31-01-19	2019-01-Jan	2019	2019-Q1	27	01-Jan	Q1	7 Sun
33						02-Feb-19	01-02-19	28-02-19	2019-02-Feb	2019	2019-Q1	2	02-Feb	Q1	6 Sat
34						30-May-20	01-05-20	31-05-20	2020-05-May	2020	2020-Q2	30	05-May	Q2	6 Sat
35						12-Oct-20	01-10-20	31-10-20	2020-10-Oct	2020	2020-Q4	12	10-Oct	Q4	1 Mon
36						10-Oct-20	01-10-20	31-10-20	2020-10-Oct	2020	2020-Q4	10	10-Oct	Q4	6 Sat
37						08-Apr-18	01-04-18	30-04-18	2018-04-Apr	2018	2018-Q2	8	04-Apr	Q2	7 Sun
38						03-Apr-21	01-04-21	30-04-21	2021-04-Apr	2021	2021-Q2	3	04-Apr	Q2	6 Sat
39						11-Dec-18	01-12-18	31-12-18	2018-12-Dec	2018	2018-Q4	11	12-Dec	Q4	2 Tue
40						31-Mar-19	01-03-19	31-03-19	2019-03-Mar	2019	2019-Q1	31	03-Mar	Q1	7 Sun
41						10-Jul-20	01-07-20	31-07-20	2020-07-Jul	2020	2020-Q3	10	07-Jul	Q3	5 Fri
42						18-Oct-18	01-10-18	31-10-18	2018-10-Oct	2018	2018-Q4	18	10-Oct	Q4	4 Thu
43
ADATE new

Cell Formulas
Range	Formula
G1	G1	=FORMULATEXT(G3)
A2	A2	=FORMULATEXT(F3)
F3:F42	F3	=RANDARRAY(40,,"1-jan-18",TODAY(),1)
G3:O42	G3	=ADATE($F$3#,G2,)
Dynamic array formulas.

 

[Xlambda]

Happy 4th of July !!!
To celebrate, here is a function to help us find dates based on any weekday occurrence pattern, like 2nd Monday after a certain date or of a certain month etc.
Or find n'th Sunday before or after a certain date that can be any date, the beginning, or the end of the current month of that date.
Inspired by: Find the Last Sunday Of the Month
Important note: The function does not use WEEKDAY function at all. return_type argument can mess up the results if not used with care. WEEKDAY can be used only when sorting is involved.
Find Weekday Function
FNDWDY(da,[wd],[nr],[cm],[df])
da:dates array, any array (starting date reference)
[wd]: weekday, string 3 letters; mon or MON or Mon for Monday etc.
- if omitted, wd value will be the weekday of respective date delivered by date array "da"
[nr]: the nr'th occurrence of "wd"; if nr<0, before the date; if nr>0 after the date
- if omitted => nr=1 => first occurrence after the date
[cm]: current month argument
- if omitted or 0 starting date will be considered the date delivered by "da"
- if 1 or <>0, if nr>0, starting date will be considered first day of current month ; if nr<0, starting date will be considered the end of current month
[df]: date format
- if omitted, date result returned in this format "Mon 04-05-2022" or "*Mon 04-05-2022" (text format)
The "*" represents that the result's month is different to the current month
- if 1, date numeric format

=LAMBDA(da, [wd], [nr], [cm], [df],
    LET(
        m, MAP(
            da,
            LAMBDA(x,
                LET(
                    n, IF(nr, nr, 1),
                    g, SIGN(n),
                    r, n * g,
                    f, DATE(YEAR(x), MONTH(x), 1),
                    e, EOMONTH(f, 0),
                    s, SWITCH(
                        wd,
                        1,
                        f,
                        2,
                        e,
                        LET(
                            b, IF(cm, IF(g = -1, e, f), x),
                            ad, SEQUENCE(7 * (r - 1) + 7, , b, g),
                            w, IF(wd = 0, TEXT(x, "ddd"), wd),
                            INDEX(FILTER(ad, TEXT(ad, "ddd") = w), r)
                        )
                    ),
                    t, IF(df, s, TEXT(s, "ddd dd-mm-yyyy")),
                    IF(MONTH(s) = MONTH(x), t, IF(df, t, "*" & t))
                )
            )
        ),
        IF(da = "", "", IFERROR(m, ""))
    )
)

ADATE.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1					First, find weekday of 4'th July (also simple TEXT will do)
2	Holidays in US that require weekday calculations										weekday first of month		weekday end of month
3							all arg omitted		da		=FNDWDY(I4:I15)		=FNDWDY(EOMONTH(+I4:I15,0))
4		Holiday	find pattern				=FNDWDY(E5:E15)		01-01-22		Sat 01-01-2022		Mon 31-01-2022
5	1	Martin Luther King Jr.	3rd Mon Jan	da	04-07-20		Sat 04-07-2020		01-02-22		Tue 01-02-2022		Mon 28-02-2022
6	2	Washington's Birthday	3rd Mon Feb		04-07-21		Sun 04-07-2021		01-03-22		Tue 01-03-2022		Thu 31-03-2022
7	3	Memorial Day	last Mon May		04-07-22		Mon 04-07-2022		01-04-22		Fri 01-04-2022		Sat 30-04-2022
8	4	Labor Day	1st Mon Sep		04-07-23		Tue 04-07-2023		01-05-22		Sun 01-05-2022		Tue 31-05-2022
9	5	Columbus Day	2nd Mon Oct		04-07-24		Thu 04-07-2024		01-06-22		Wed 01-06-2022		Thu 30-06-2022
10	6	Thanksgiving day	4th Thu Nov		04-07-25		Fri 04-07-2025		01-07-22		Fri 01-07-2022		Sun 31-07-2022
11	7	President's day	3rd Mon Feb		04-07-26		Sat 04-07-2026		01-08-22		Mon 01-08-2022		Wed 31-08-2022
12	8	Mother's day	2nd Sun May		04-07-27		Sun 04-07-2027		01-09-22		Thu 01-09-2022		Fri 30-09-2022
13	9	Black Friday	day after TKG		04-07-28		Tue 04-07-2028		01-10-22		Sat 01-10-2022		Mon 31-10-2022
14	10	Cyber Monday	1 Mon after TKG		04-07-29		Wed 04-07-2029		01-11-22		Tue 01-11-2022		Wed 30-11-2022
15					04-07-30		Thu 04-07-2030		01-12-22		Thu 01-12-2022		Sat 31-12-2022
16		TKG-Thanksgiving date
17
18					1. MLK Jr. - 3rd Mon Jan				if any date of Jan we can use cm=1 argument				if cm omitted, wrong results
19									ignores current day, counts from 1st of month				cm,1 and nr=3 (>0) => start date for sure will be beginning of month
20							=FNDWDY(E21:E31,"mon",3)		da		=FNDWDY(I21:I31,"mon",3,1)		=FNDWDY(I21:I31,"mon",3)
21				da	01-01-20		Mon 20-01-2020		12-01-20		Mon 20-01-2020		Mon 27-01-2020
22					01-01-21		Mon 18-01-2021		07-01-21		Mon 18-01-2021		Mon 25-01-2021
23					01-01-22		Mon 17-01-2022		18-01-22		Mon 17-01-2022		*Mon 07-02-2022
24					01-01-23		Mon 16-01-2023		02-01-23		Mon 16-01-2023		Mon 16-01-2023
25					01-01-24		Mon 15-01-2024		11-01-24		Mon 15-01-2024		Mon 29-01-2024
26					01-01-25		Mon 20-01-2025		31-01-25		Mon 20-01-2025		*Mon 17-02-2025
27					01-01-26		Mon 19-01-2026		19-01-26		Mon 19-01-2026		*Mon 02-02-2026
28					01-01-27		Mon 18-01-2027		09-01-27		Mon 18-01-2027		Mon 25-01-2027
29					01-01-28		Mon 17-01-2028		27-01-28		Mon 17-01-2028		*Mon 14-02-2028
30					01-01-29		Mon 15-01-2029		23-01-29		Mon 15-01-2029		*Mon 12-02-2029
31					01-01-30		Mon 21-01-2030		14-01-30		Mon 21-01-2030		Mon 28-01-2030
32
FNDWDY

Cell Formulas
Range	Formula
M3,K20,M20,G20,K3,G4	M3	=FORMULATEXT(M4)
K4:K15	K4	=FNDWDY(I4:I15)
M4:M15	M4	=FNDWDY(EOMONTH(+I4:I15,0))
G5:G15	G5	=FNDWDY(E5:E15)
G21:G31	G21	=FNDWDY(E21:E31,"mon",3)
K21:K31	K21	=FNDWDY(I21:I31,"mon",3,1)
M21:M31	M21	=FNDWDY(I21:I31,"mon",3)
Dynamic array formulas.

 

[Xlambda]

ADATE.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2		Last Sun of month			2. Washington's Birthday - 3rd Mon Feb				3. Memorial Day - last Mon May
3		da,2D array			=DATE(SEQUENCE(7,,2020),2,1)				=DATE(SEQUENCE(7,,2020),SEQUENCE(7,,5,0),1)
4		04-07-22	12-01-23		↓↓↓		=FNDWDY(E5#,"Mon",3)				=FNDWDY(I5#,"mon",-1,1)
5		18-08-22	abcd		01-02-20		Mon 17-02-2020		01-05-20		Mon 25-05-2020
6		04-09-22	24-02-23		01-02-21		Mon 15-02-2021		01-05-21		Mon 31-05-2021
7		23-10-22			01-02-22		Mon 21-02-2022		01-05-22		Mon 30-05-2022
8		29-11-22	08-03-23		01-02-23		Mon 20-02-2023		01-05-23		Mon 29-05-2023
9		13-12-22	17-04-23		01-02-24		Mon 19-02-2024		01-05-24		Mon 27-05-2024
10					01-02-25		Mon 17-02-2025		01-05-25		Mon 26-05-2025
11		=FNDWDY(B4:C9,"sun",-1,1)			01-02-26		Mon 16-02-2026		01-05-26		Mon 25-05-2026
12		Sun 31-07-2022	Sun 29-01-2023
13		Sun 28-08-2022			Creating random dates of nov for a sequence of years				6. Thanksgiving day - 4th Thu Nov
14		Sun 25-09-2022	Sun 26-02-2023		=DATE(SEQUENCE(7,,2020),11,RANDBETWEEN(1,30))
15		Sun 30-10-2022			↓↓↓						=FNDWDY(I16#,"thu",4,1)
16		Sun 27-11-2022	Sun 26-03-2023		04-11-20				19-11-20		Thu 26-11-2020
17		Sun 25-12-2022	Sun 30-04-2023		04-11-21				21-11-21		Thu 25-11-2021
18					04-11-22				02-11-22		Thu 24-11-2022
19		2 Sun before starting date			04-11-23				29-11-23		Thu 23-11-2023
20		=FNDWDY(B4:C9,"sun",-2)			04-11-24				14-11-24		Thu 28-11-2024
21		*Sun 26-06-2022	Sun 01-01-2023		04-11-25				17-11-25		Thu 27-11-2025
22		Sun 07-08-2022			04-11-26				23-11-26		Thu 26-11-2026
23		*Sun 28-08-2022	Sun 12-02-2023					I16:
24		Sun 16-10-2022			formula E16 produces same days,			=ATEXTSPILL(SEQUENCE(7),LAMBDA(x,DATE(2019+x,11,RANDBETWEEN(1,30))))
25		Sun 20-11-2022	*Sun 26-02-2023		does not recalculate / each row =>			with ATEXTSPILL we can creade random days
26		Sun 04-12-2022	Sun 09-04-2023
27					9. Black Friday - day after TKG
28					random days Nov
29		other functions			=ATEXTSPILL(SEQUENCE(7),LAMBDA(x,DATE(2019+x,11,RANDBETWEEN(1,30))))
30		ATEXTSPILL			↓↓↓		df,1 => dates in numeric format, we can add 1
31					↓↓↓		=FNDWDY(E32#,"Thu",4,1,1)+1
32					17-11-20		27-11-20
33					24-11-21		26-11-21		10. Cyber Monday - 1 Mon after TKG, 2 ways with nested FNDWDY
34					22-11-22		25-11-22		random days Nov
35					09-11-23		24-11-23		=ATEXTSPILL(SEQUENCE(7),LAMBDA(x,DATE(2019+x,11,RANDBETWEEN(1,30))))
36					02-11-24		29-11-24		↓↓↓		=FNDWDY(I37#,"thu",4,1,1)+4		=FNDWDY(FNDWDY(I37#,"thu",4,1,1),"mon")
37					15-11-25		28-11-25		03-11-20		30-11-20		Mon 30-11-2020
38					05-11-26		27-11-26		14-11-21		29-11-21		Mon 29-11-2021
39									28-11-22		28-11-22		Mon 28-11-2022
40									30-11-23		27-11-23		Mon 27-11-2023
41									01-11-24		02-12-24		*Mon 02-12-2024
42									19-11-25		01-12-25		*Mon 01-12-2025
43									23-11-26		30-11-26		Mon 30-11-2026
44
45													"*" => Cyber Monday not in Nov
46
FNDWDY 2

Cell Formulas
Range	Formula
E3,I35,E14,I3	E3	=FORMULATEXT(E5)
G4,K36,M36,G31,B20,K15,B11,K4	G4	=FORMULATEXT(G5)
E5:E11	E5	=DATE(SEQUENCE(7,,2020),2,1)
G5:G11	G5	=FNDWDY(E5#,"Mon",3)
I5:I11	I5	=DATE(SEQUENCE(7,,2020),SEQUENCE(7,,5,0),1)
K5:K11	K5	=FNDWDY(I5#,"mon",-1,1)
B12:C17	B12	=FNDWDY(B4:C9,"sun",-1,1)
E16:E22	E16	=DATE(SEQUENCE(7,,2020),11,RANDBETWEEN(1,30))
I16:I22,I37:I43,E32:E38	I16	=ATEXTSPILL(SEQUENCE(7),LAMBDA(x,DATE(2019+x,11,RANDBETWEEN(1,30))))
K16:K22	K16	=FNDWDY(I16#,"thu",4,1)
B21:C26	B21	=FNDWDY(B4:C9,"sun",-2)
H24	H24	=FORMULATEXT(I16)
E29	E29	=FORMULATEXT(E32)
G32:G38	G32	=FNDWDY(E32#,"Thu",4,1,1)+1
K37:K43	K37	=FNDWDY(I37#,"thu",4,1,1)+4
M37:M43	M37	=FNDWDY(FNDWDY(I37#,"thu",4,1,1),"mon")
Dynamic array formulas.

 

[Xlambda]

Due date example.

ADATE.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Task. Calculating due date									single cell
2	Rules:	If invoice issued from Mon to Wed due date 3 weeks from Wed								lambda formula
3		If invoice issued from Thu to Sun due date 4 weeks from Wed							J8:	=LET(a,B8#,w,WEEKDAY(a,2),MAP(a,w,LAMBDA(x,y,FNDWDY(x,"wed",IF(y<3,3,4),,1))))
4										↓↓↓
5						=IF(D8#<3,FNDWDY(B8#,"wed",3),FNDWDY(B8#,"wed",4))				↓↓↓
6		=RANDARRAY(50,,"5-7-22","31-12-22",1)				↓↓↓	or	=MAP(B8#,D8#,LAMBDA(x,y,FNDWDY(x,"wed",IF(y<3,3,4))))
7		Inv Date		=WEEKDAY(B8#,2)		↓↓↓		↓↓↓		Due date (numeric date format)
8		15-08-22		1		Wed 31-08-2022		Wed 31-08-2022		31-08-22
9		12-09-22		1		Wed 28-09-2022		Wed 28-09-2022		28-09-22
10		19-08-22		5		*Wed 14-09-2022		*Wed 14-09-2022		14-09-22
11		12-09-22		1		Wed 28-09-2022		Wed 28-09-2022		28-09-22
12		27-12-22		2		*Wed 11-01-2023		*Wed 11-01-2023		11-01-23
13		10-11-22		4		*Wed 07-12-2022		*Wed 07-12-2022		07-12-22
14		07-08-22		7		Wed 31-08-2022		Wed 31-08-2022		31-08-22
15		29-07-22		5		*Wed 24-08-2022		*Wed 24-08-2022		24-08-22
16		29-07-22		5		*Wed 24-08-2022		*Wed 24-08-2022		24-08-22
17		21-08-22		7		*Wed 14-09-2022		*Wed 14-09-2022		14-09-22
18		25-07-22		1		*Wed 10-08-2022		*Wed 10-08-2022		10-08-22
19		06-10-22		4		*Wed 02-11-2022		*Wed 02-11-2022		02-11-22
20		07-09-22		3		Wed 28-09-2022		Wed 28-09-2022		28-09-22
21		30-10-22		7		*Wed 23-11-2022		*Wed 23-11-2022		23-11-22
22		23-10-22		7		*Wed 16-11-2022		*Wed 16-11-2022		16-11-22
23		06-12-22		2		Wed 21-12-2022		Wed 21-12-2022		21-12-22
24		26-11-22		6		*Wed 21-12-2022		*Wed 21-12-2022		21-12-22
25		12-10-22		3		*Wed 02-11-2022		*Wed 02-11-2022		02-11-22
26		27-07-22		3		*Wed 17-08-2022		*Wed 17-08-2022		17-08-22
27		04-10-22		2		Wed 19-10-2022		Wed 19-10-2022		19-10-22
28		23-11-22		3		*Wed 14-12-2022		*Wed 14-12-2022		14-12-22
29		18-11-22		5		*Wed 14-12-2022		*Wed 14-12-2022		14-12-22
30		17-07-22		7		*Wed 10-08-2022		*Wed 10-08-2022		10-08-22
31		30-09-22		5		*Wed 26-10-2022		*Wed 26-10-2022		26-10-22
32		08-09-22		4		*Wed 05-10-2022		*Wed 05-10-2022		05-10-22
33		07-08-22		7		Wed 31-08-2022		Wed 31-08-2022		31-08-22
34		28-12-22		3		*Wed 18-01-2023		*Wed 18-01-2023		18-01-23
35		03-09-22		6		Wed 28-09-2022		Wed 28-09-2022		28-09-22
36		24-12-22		6		*Wed 18-01-2023		*Wed 18-01-2023		18-01-23
37		27-09-22		2		*Wed 12-10-2022		*Wed 12-10-2022		12-10-22
38		15-07-22		5		*Wed 10-08-2022		*Wed 10-08-2022		10-08-22
39		24-11-22		4		*Wed 21-12-2022		*Wed 21-12-2022		21-12-22
40		29-07-22		5		*Wed 24-08-2022		*Wed 24-08-2022		24-08-22
41		05-10-22		3		Wed 26-10-2022		Wed 26-10-2022		26-10-22
42		18-07-22		1		*Wed 03-08-2022		*Wed 03-08-2022		03-08-22
43		09-08-22		2		Wed 24-08-2022		Wed 24-08-2022		24-08-22
44		24-09-22		6		*Wed 19-10-2022		*Wed 19-10-2022		19-10-22
45		31-12-22		6		*Wed 25-01-2023		*Wed 25-01-2023		25-01-23
46		20-09-22		2		*Wed 05-10-2022		*Wed 05-10-2022		05-10-22
47		18-12-22		7		*Wed 11-01-2023		*Wed 11-01-2023		11-01-23
48		22-09-22		4		*Wed 19-10-2022		*Wed 19-10-2022		19-10-22
49		01-11-22		2		Wed 16-11-2022		Wed 16-11-2022		16-11-22
50		12-08-22		5		*Wed 07-09-2022		*Wed 07-09-2022		07-09-22
51		15-10-22		6		*Wed 09-11-2022		*Wed 09-11-2022		09-11-22
52		13-11-22		7		*Wed 07-12-2022		*Wed 07-12-2022		07-12-22
53		21-07-22		4		*Wed 17-08-2022		*Wed 17-08-2022		17-08-22
54		08-07-22		5		*Wed 03-08-2022		*Wed 03-08-2022		03-08-22
55		09-09-22		5		*Wed 05-10-2022		*Wed 05-10-2022		05-10-22
56		20-09-22		2		*Wed 05-10-2022		*Wed 05-10-2022		05-10-22
57		30-09-22		5		*Wed 26-10-2022		*Wed 26-10-2022		26-10-22
58
Due Date

Cell Formulas
Range	Formula
J3	J3	=FORMULATEXT(J8)
F5	F5	=FORMULATEXT(F8)
B6,H6	B6	=FORMULATEXT(B8)
D7	D7	=FORMULATEXT(D8)
B8:B57	B8	=RANDARRAY(50,,"5-7-22","31-12-22",1)
D8:D57	D8	=WEEKDAY(B8#,2)
F8:F57	F8	=IF(D8#<3,FNDWDY(B8#,"wed",3),FNDWDY(B8#,"wed",4))
H8:H57	H8	=MAP(B8#,D8#,LAMBDA(x,y,FNDWDY(x,"wed",IF(y<3,3,4))))
J8:J57	J8	=LET(a,B8#,w,WEEKDAY(a,2),MAP(a,w,LAMBDA(x,y,FNDWDY(x,"wed",IF(y<3,3,4),,1))))
Dynamic array formulas.

 

[Xlambda]

Epic video, latest, Mike's YT Date & Time Formulas & Functions in Excel Worksheet and Power Query - 365 MECS 05
At min 38.07, about quarters, fiscal years, formulas and PQ
Covered before a quarter function APIVOT post #17
QTR(ar,[qt],[sm]) (added error management and new argument)
ar: dates array, (could be single value, cell reference, column/row vectors or even 2D arrays)
[qt]: quarter type argument, (one of these values "q", "qy", fq", "fy", "fqy" )
[sm]: start month of fiscal year - if sm is omitted, default value is 4, if we need 7 we input 7
qt quarter type argument values:
- if qt is omitted, default value = "qy" => this format ex: 2022 Q4 (always year first, for sorting versatility)
"q" ,calendar quarter => format ex: Q4
"qy" ,calendar quarter and year => format ex: 2022 Q4
"fq" ,fiscal quarter => format ex: FQ4
"fy" ,fiscal year => format ex: FY 2023 (fiscal year 2023-24)
"fqy" ,fiscal quarter and year => format ex: FY 2023 Q4
If none of the above, the function will return the value for "fqy"
Could have used numeric values instead of text, but with letters it is easier to remember.

=LAMBDA(ar, [qt], [sm],
    LET(
        s, IF(sm, sm, 4),
        a, IF(ar = "", "", ar),
        m, MONTH(a),
        y, YEAR(a),
        q, "Q" & MONTH(m & 0),
        qy, y & " " & q,
        fq, "Q" & MONTH(MOD(m - s, 12) + 1 & 0),
        fy, "FY " & y + (m >= s) - 1,
        fqy, fy & " " & fq,
        IFERROR(SWITCH(qt, 0, qy, "q", q, "qy", qy, "fq", "F" & fq, "fy", fy, "fqy", fqy),"")
    )
)

05-M365ExcelClass.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1		April 1 is 1st day in Fiscal Year						Mike's data
2		Date Worksheet Formulas to Create Data Attributes for a Table of Sales Data
3		Get Year, Month, Day and Quarter Data from a Serial Number:											=QTR(S[Date],"qy")				=QTR(S[Date],"fy")
4		Date	YEAR	MONTH	Quarter	Q Label	Fiscal Quarter	Fiscal Year	F Q Label		=QTR(S[Date],"q")		↓↓↓		=QTR(S[Date],"fq")		↓↓↓		=QTR(S[Date],"fqy")
5		19-04-23	2023	4	2	Q - 2	1	2023	2023 - Q 1		Q2		2023 Q2		FQ1		FY 2023		FY 2023 Q1
6		26-01-23	2023	1	1	Q - 1	4	2022	2022 - Q 4		Q1		2023 Q1		FQ4		FY 2022		FY 2022 Q4
7		28-02-23	2023	2	1	Q - 1	4	2022	2022 - Q 4		Q1		2023 Q1		FQ4		FY 2022		FY 2022 Q4
8		18-10-22	2022	10	4	Q - 4	3	2022	2022 - Q 3		Q4		2022 Q4		FQ3		FY 2022		FY 2022 Q3
9		27-02-22	2022	2	1	Q - 1	4	2021	2021 - Q 4		Q1		2022 Q1		FQ4		FY 2021		FY 2021 Q4
10		07-04-22	2022	4	2	Q - 2	1	2022	2022 - Q 1		Q2		2022 Q2		FQ1		FY 2022		FY 2022 Q1
11		24-12-22	2022	12	4	Q - 4	3	2022	2022 - Q 3		Q4		2022 Q4		FQ3		FY 2022		FY 2022 Q3
12		05-05-22	2022	5	2	Q - 2	1	2022	2022 - Q 1		Q2		2022 Q2		FQ1		FY 2022		FY 2022 Q1
13		21-02-22	2022	2	1	Q - 1	4	2021	2021 - Q 4		Q1		2022 Q1		FQ4		FY 2021		FY 2021 Q4
14		01-07-23	2023	7	3	Q - 3	2	2023	2023 - Q 2		Q3		2023 Q3		FQ2		FY 2023		FY 2023 Q2
15		31-01-22	2022	1	1	Q - 1	4	2021	2021 - Q 4		Q1		2022 Q1		FQ4		FY 2021		FY 2021 Q4
16		15-03-23	2023	3	1	Q - 1	4	2022	2022 - Q 4		Q1		2023 Q1		FQ4		FY 2022		FY 2022 Q4
17		23-11-22	2022	11	4	Q - 4	3	2022	2022 - Q 3		Q4		2022 Q4		FQ3		FY 2022		FY 2022 Q3
18		27-12-22	2022	12	4	Q - 4	3	2022	2022 - Q 3		Q4		2022 Q4		FQ3		FY 2022		FY 2022 Q3
19		15-11-22	2022	11	4	Q - 4	3	2022	2022 - Q 3		Q4		2022 Q4		FQ3		FY 2022		FY 2022 Q3
20		23-06-23	2023	6	2	Q - 2	1	2023	2023 - Q 1		Q2		2023 Q2		FQ1		FY 2023		FY 2023 Q1
21		10-09-23	2023	9	3	Q - 3	2	2023	2023 - Q 2		Q3		2023 Q3		FQ2		FY 2023		FY 2023 Q2
22		26-02-22	2022	2	1	Q - 1	4	2021	2021 - Q 4		Q1		2022 Q1		FQ4		FY 2021		FY 2021 Q4
23		17-03-23	2023	3	1	Q - 1	4	2022	2022 - Q 4		Q1		2023 Q1		FQ4		FY 2022		FY 2022 Q4
24		16-12-22	2022	12	4	Q - 4	3	2022	2022 - Q 3		Q4		2022 Q4		FQ3		FY 2022		FY 2022 Q3
25		31-01-23	2023	1	1	Q - 1	4	2022	2022 - Q 4		Q1		2023 Q1		FQ4		FY 2022		FY 2022 Q4
26		10-03-23	2023	3	1	Q - 1	4	2022	2022 - Q 4		Q1		2023 Q1		FQ4		FY 2022		FY 2022 Q4
27		06-12-22	2022	12	4	Q - 4	3	2022	2022 - Q 3		Q4		2022 Q4		FQ3		FY 2022		FY 2022 Q3
28		08-07-23	2023	7	3	Q - 3	2	2023	2023 - Q 2		Q3		2023 Q3		FQ2		FY 2023		FY 2023 Q2
29		21-04-22	2022	4	2	Q - 2	1	2022	2022 - Q 1		Q2		2022 Q2		FQ1		FY 2022		FY 2022 Q1
30		22-06-22	2022	6	2	Q - 2	1	2022	2022 - Q 1		Q2		2022 Q2		FQ1		FY 2022		FY 2022 Q1
31		26-04-22	2022	4	2	Q - 2	1	2022	2022 - Q 1		Q2		2022 Q2		FQ1		FY 2022		FY 2022 Q1
32		04-12-23	2023	12	4	Q - 4	3	2023	2023 - Q 3		Q4		2023 Q4		FQ3		FY 2023		FY 2023 Q3
33		17-05-22	2022	5	2	Q - 2	1	2022	2022 - Q 1		Q2		2022 Q2		FQ1		FY 2022		FY 2022 Q1
34		18-06-22	2022	6	2	Q - 2	1	2022	2022 - Q 1		Q2		2022 Q2		FQ1		FY 2022		FY 2022 Q1
35		11-11-22	2022	11	4	Q - 4	3	2022	2022 - Q 3		Q4		2022 Q4		FQ3		FY 2022		FY 2022 Q3
36		13-11-22	2022	11	4	Q - 4	3	2022	2022 - Q 3		Q4		2022 Q4		FQ3		FY 2022		FY 2022 Q3
37		23-05-22	2022	5	2	Q - 2	1	2022	2022 - Q 1		Q2		2022 Q2		FQ1		FY 2022		FY 2022 Q1
38		19-01-22	2022	1	1	Q - 1	4	2021	2021 - Q 4		Q1		2022 Q1		FQ4		FY 2021		FY 2021 Q4
39		11-08-22	2022	8	3	Q - 3	2	2022	2022 - Q 2		Q3		2022 Q3		FQ2		FY 2022		FY 2022 Q2
40		17-01-23	2023	1	1	Q - 1	4	2022	2022 - Q 4		Q1		2023 Q1		FQ4		FY 2022		FY 2022 Q4
41		24-02-22	2022	2	1	Q - 1	4	2021	2021 - Q 4		Q1		2022 Q1		FQ4		FY 2021		FY 2021 Q4
42		12-02-23	2023	2	1	Q - 1	4	2022	2022 - Q 4		Q1		2023 Q1		FQ4		FY 2022		FY 2022 Q4
43		11-06-22	2022	6	2	Q - 2	1	2022	2022 - Q 1		Q2		2022 Q2		FQ1		FY 2022		FY 2022 Q1
44		13-07-23	2023	7	3	Q - 3	2	2023	2023 - Q 2		Q3		2023 Q3		FQ2		FY 2023		FY 2023 Q2
DateFields (an)

Cell Formulas
Range	Formula
M3,Q3	M3	=FORMULATEXT(M5)
K4,S4,O4	K4	=FORMULATEXT(K5)
C5:C44	C5	=YEAR([@Date])
D5:D44	D5	=MONTH([@Date])
E5:E44	E5	=ROUNDUP([@MONTH]/3,0)
F5:F44	F5	="Q - "&[@Quarter]
G5:G44	G5	=IF([@MONTH]<4,4,[@Quarter]-1)
H5:H44	H5	=IF([@MONTH]<4,[@YEAR]-1,[@YEAR])
I5:I44	I5	=[@[Fiscal Year]]&" - Q "&[@[Fiscal Quarter]]
K5:K147	K5	=QTR(S[Date],"q")
M5:M147	M5	=QTR(S[Date],"qy")
O5:O147	O5	=QTR(S[Date],"fq")
Q5:Q147	Q5	=QTR(S[Date],"fy")
S5:S147	S5	=QTR(S[Date],"fqy")
Dynamic array formulas.

 

[Xlambda]

A very cool challenge emerged from the comment section same YT from Mark Thompson:
".... One topic eludes me though… I do archival work on old collections for family history research, and I am constantly tripped up when working with dates before 1900.
Do you have, or know of a good video/blog for effectively dealing with dates prior to 1900 in either Excel or PQ? As they always format as strings, I can’t do date math or sorts with them, especially when they are included with other dates after 1900. Surely someone else has solved this… or, I’m missing something easy? Everything I’ve come up with so far is a messy hack."
I had several approaches to solve this, this is, I think, the best one: Continuing the sequence of dates serial numbers for dates before 1Jan1900 with negative numbers.

First, about leap years. Quotes from List of leap years and leap days since 1600 and from Wikipedia.
"Every year that is exactly divisible by four is a leap year, except for years that are exactly divisible by 100, but these centurial years are leap years if they are exactly divisible by 400. For example, the years 1700, 1800, and 1900 are not leap years, but the years 1600 and 2000 are."
"The Gregorian calendar (introduced in Oct 1582) omits three leap days every 400 years, which is the length of its leap cycle. This is done by omitting February 29 in the three century years (multiples of 100) that are not multiples of 400. The years 2000 and 2400 are leap years, but not 1700, 1800, 1900, 2100, 2200 and 2300"

Excel "anomaly": Excel calculates the year 1900 as a leap year but it wasn't. Excel incorrectly assumes that the year 1900 is a leap year
Because of this, weekdays before 29Feb1990 are offset by 1 day => =TEXT("1-1-1900","ddd") returns Sunday instead of Monday.
If 1Jan1900 was a Monday, then, a day before that, 31Dec1899, was a Sunday.
Starting with1Mar1900, weekdays align.
NDT(y,m,d) the Negative DaTe function. Same arguments as DATE function.
y: year value or years array; 1600<=y< 9999
m: month value or months array
d: day value or days array

=LAMBDA(y, m, d,
    IF(y >= 1900, DATE(y, m, d), IF(y < 1600, NA(), DATE(y + 400, m, d) - 146098))
)

Neg sn dates.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1		NDT(y,m,d) Concept,Negative dates/sn Concept										Concept of symmetric leap years distributions (1600 to 1899 vs 2000 to 2299)
2		Array of dates variables in descending order										1600-1899		2000-2299
3		back in the past to 1Jan1600										=LET(a,SEQUENCE(300,,1600),FILTER(a,(MOD(a,4)=0)*(a<>1700)*(a<>1800)))
4						excel	real					↓↓↓		=LET(a,SEQUENCE(300,,2000),FILTER(a,(MOD(a,4)=0)*(a<>2100)*(a<>2200)))
5		Year	Month	Day	sn	wkdy	wkdy					1600		2000			check symmetry
6		2299	12	31	146098	Sun	Sun	┐―	┐			1604		2004			=UNIQUE(N5#-L5#)
7		2299	12	30	146097	Sat	Sat	│	│			1608		2008			400
8								Ͱ 109573				1612		2012			If leap years distribution is symmetric
9		-	-	-	-	-	-	│	│			1616		2016			means that distribution of months and days is identical.
10		2000	1	2	36527	Sun	Sun	│	│			1620		2020			=> days btw 1Jan2000 and 31Dec2299 =
11		2000	1	1	36526	Sat	Sat	┘	│			1624		2024			=days btw 1Jan1600 and 31Dec1899 =
12		-	-	-	-	-	-		│ days gap			1628		2028			="31-12-2299"-"1-1-2000"+1
13		-	-	-	-	-	-		Ͱ 146098			1632		2032			109573
14		1900	3	1	61	Thu	Thu		│			1636		2036			"?" sn calculation sn(1Jan1600)
15		1900	2	29	60	Wed	x		│			1640		2040			0-"?"+1=109573
16		1900	2	28	59	Tue	Wed		│			1644		2044		?	=> "?" =sn(1Jan1600)= - 109572
17		-	-	-	-	-	-		│			1648		2048
18		-	-	-	-	-	-		│			1652		2052			Years gap 2000-1600=400 years
19		1900	1	2	2	Mon	Tue		│			1656		2056			=2000-1600
20		1900	1	1	1	Sun	Mon		│			1660		2060			400
21		1899	12	31	0	(Sat)	Sun	┐―	┘			1664		2064			Days gap =DATE(2000,1,1)-sn(1Jan1600)
22		1899	12	30	-1		Sat	│				1668		2068			=DATE(2000,1,1)-(-109572)
23		1899	12	29	-2		Fri	│				1672		2072			146098
24		-	-	-	-		-	Ͱ 109573				1676		2076
25		-	-	-	-		-	│				1680		2080			core expresion of NDT function
26		1600	1	2	-		-	│				1684		2084			Serial number (sn) of a date before 1900 (1600<=y<1900)
27		1600	1	1	?		?	┘				1688		2088			=ndt(y,m,d)=date(y+400,m,d)-146098
28												1692		2092
29												1696		2096			Weekdays distribution.
30												1704		2104			wkdy of 1Jan1990 (sn=1)
31												1708		2108			=TEXT("1-1-1900","ddd")
32												1712		2112			Sun	=> in reality was a Mon
33												1716		2116			=> for sn=0 (31Dec1899) weekday was Sun
34												1720		2120			400 years after 31Dec1899 => 31Dec2299
35												1724		2124			=TEXT("31-12-2299","ddd")
36												1728		2128			Sun	=> also weekdays distribution is identical
37												1732		2132			=> wkdy(1Jan1600)=wkdy(1Jan1600+400)
38												1736		2136			=TEXT("1-1-2000","ddd")
39												1740		2140		?	Sat
40												1744		2144
41												1748		2148
42												1752		2152
43												1756		2156
44												1760		2160
45												1764		2164
46												1768		2168
47												1772		2172
48												1776		2176
49												1780		2180
50												1784		2184
51												1788		2188
52												1792		2192
53												1796		2196
54												1804		2204
55												1808		2208
56												1812		2212
57												1816		2216
58												1820		2220
59												1824		2224
60												1828		2228
61												1832		2232
62												1836		2236
63												1840		2240
64												1844		2244
65												1848		2248
66												1852		2252
67												1856		2256
68												1860		2260
69												1864		2264
70												1868		2268
71												1872		2272
72												1876		2276
73												1880		2280
74												1884		2284
75												1888		2288
76												1892		2292
77												1896		2296
78
Concept

Cell Formulas
Range	Formula
L3	L3	=FORMULATEXT(L5)
N4,Q38,Q35,Q31,Q22,Q19,Q12,Q6	N4	=FORMULATEXT(N5)
L5:L77	L5	=LET(a,SEQUENCE(300,,1600),FILTER(a,(MOD(a,4)=0)*(a<>1700)*(a<>1800)))
N5:N77	N5	=LET(a,SEQUENCE(300,,2000),FILTER(a,(MOD(a,4)=0)*(a<>2100)*(a<>2200)))
Q7	Q7	=UNIQUE(N5#-L5#)
Q13	Q13	="31-12-2299"-"1-1-2000"+1
Q20	Q20	=2000-1600
Q23	Q23	=DATE(2000,1,1)-(-109572)
Q32	Q32	=TEXT("1-1-1900","ddd")
Q36	Q36	=TEXT("31-12-2299","ddd")
Q39	Q39	=TEXT("1-1-2000","ddd")
Dynamic array formulas.

 

[Xlambda]

Neg sn dates.xlsx
	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
1		The function NDT(y,m,d)					(like DATE function, NDT can handle array arguments)
2							=NDT(B5:B18,C5:C18,D5:D18)
3							↓↓↓		1st of every month for each year													serial numbers of 1st of Apr for years interval 1880-1910
4		Year	Month	Day	sn		↓↓↓		=NDT(B5:B18,SEQUENCE(,12),1)													=NDT(SEQUENCE(31,,1880),4,1)
5		2299	12	31	146098		146098		145734	145765	145793	145824	145854	145885	145915	145946	145977	146007	146038	146068		-7213
6		2299	12	30	146097		146097		145734	145765	145793	145824	145854	145885	145915	145946	145977	146007	146038	146068		-6848
7		2000	1	2	36527		36527		36526	36557	36586	36617	36647	36678	36708	36739	36770	36800	36831	36861		-6483
8		2000	1	1	36526		36526		36526	36557	36586	36617	36647	36678	36708	36739	36770	36800	36831	36861		-6118
9		1900	3	1	61		61		1	32	61	92	122	153	183	214	245	275	306	336		-5752
10		1900	2	29	60		60		1	32	61	92	122	153	183	214	245	275	306	336		-5387
11		1900	2	28	59		59		1	32	61	92	122	153	183	214	245	275	306	336		-5022
12		1900	1	2	2		2		1	32	61	92	122	153	183	214	245	275	306	336		-4657
13		1900	1	1	1		1		1	32	61	92	122	153	183	214	245	275	306	336		-4291
14		1899	12	31	0		0		-364	-333	-305	-274	-244	-213	-183	-152	-121	-91	-60	-30		-3926
15		1899	12	30	-1		-1		-364	-333	-305	-274	-244	-213	-183	-152	-121	-91	-60	-30		-3561
16		1899	12	29	-2		-2		-364	-333	-305	-274	-244	-213	-183	-152	-121	-91	-60	-30		-3196
17		1600	1	2			-109571		-109572	-109541	-109512	-109481	-109451	-109420	-109390	-109359	-109328	-109298	-109267	-109237		-2830
18		1600	1	1			-109572		-109572	-109541	-109512	-109481	-109451	-109420	-109390	-109359	-109328	-109298	-109267	-109237		-2465
19																						-2100
20									first 10 days of Dec for each year													-1735
21									=NDT(B5:B18,12,SEQUENCE(,10))													-1369
22									146068	146069	146070	146071	146072	146073	146074	146075	146076	146077				-1004
23									146068	146069	146070	146071	146072	146073	146074	146075	146076	146077				-639
24									36861	36862	36863	36864	36865	36866	36867	36868	36869	36870				-274
25									36861	36862	36863	36864	36865	36866	36867	36868	36869	36870				92
26									336	337	338	339	340	341	342	343	344	345				457
27									336	337	338	339	340	341	342	343	344	345				822
28									336	337	338	339	340	341	342	343	344	345				1187
29									336	337	338	339	340	341	342	343	344	345				1553
30									336	337	338	339	340	341	342	343	344	345				1918
31									-30	-29	-28	-27	-26	-25	-24	-23	-22	-21				2283
32									-30	-29	-28	-27	-26	-25	-24	-23	-22	-21				2648
33									-30	-29	-28	-27	-26	-25	-24	-23	-22	-21				3014
34									-109237	-109236	-109235	-109234	-109233	-109232	-109231	-109230	-109229	-109228				3379
35									-109237	-109236	-109235	-109234	-109233	-109232	-109231	-109230	-109229	-109228				3744
36
NDT

Cell Formulas
Range	Formula
G2	G2	=FORMULATEXT(G5)
I4,I21,V4	I4	=FORMULATEXT(I5)
G5:G18	G5	=NDT(B5:B18,C5:C18,D5:D18)
I5:T18	I5	=NDT(B5:B18,SEQUENCE(,12),1)
V5:V35	V5	=NDT(SEQUENCE(31,,1880),4,1)
I22:R35	I22	=NDT(B5:B18,12,SEQUENCE(,10))
Dynamic array formulas.

 

[Xlambda]

Neg sn dates.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1		If,y,m,d are 2D arrays
2		Years							Months							Days
3		=RANDARRAY(10,6,1600,2022,1)							=RANDARRAY(10,6,1,12,1)							=RANDARRAY(10,6,1,31,1)
4		1748	1933	1915	1946	1642	1896		9	12	8	8	6	9		11	3	25	12	6	16
5		1765	1858	1972	1854	1724	1897		12	5	9	11	2	7		19	8	4	28	27	2
6		1983	1651	1688	1764	1803	1731		10	1	12	5	4	12		11	17	9	2	6	28
7		1992	1879	1911	1761	1944	1669		12	4	4	4	9	4		30	14	11	30	29	15
8		1825	1952	1934	1971	1908	1804		4	2	7	3	4	5		19	19	21	26	16	14
9		1720	2001	1761	1823	1947	1946		4	9	1	2	11	6		21	5	16	5	21	21
10		1715	1764	1829	1931	1824	1660		3	4	4	3	3	4		1	30	19	7	25	4
11		1966	1801	1750	1894	1629	1880		3	7	11	2	7	9		7	23	24	30	11	10
12		1818	1729	1676	1855	1755	1789		6	9	5	5	6	5		24	31	17	14	7	12
13		1779	1856	1736	1963	1820	1832		4	1	6	7	9	1		24	4	25	25	11	29
14
15		=MAP(B4#,I4#,P4#,LAMBDA(y,m,d,NDT(y,m,d)))
16		-55262	12391	5716	17026	-94075	-1201
17		-48954	-15212	26546	-16469	-64225	-912
18		30600	-90928	-77087	-49550	-35333	-61364
19		33968	-7566	4119	-50648	16344	-84265
20		-27284	19043	12621	26018	3029	-34929
21		-65632	37139	-50752	-28088	17492	16974
22		-67510	-49552	-25823	11389	-27674	-87563
23		24173	-35955	-54458	-2130	-98788	-7051
24		-29775	-62182	-81676	-16302	-52802	-40409
25		-44080	-16067	-59723	23217	-28965	-24808
26
27
28		DATE vs NDT behavior, both are consistent with "off grid" dates like 30 Feb
29
30		=DATE(2022,2,30)		=B31
31		02-03-22		44622
32				checking days "gap"
33		=NDT(2022-400,2,30)		=D31-B34
34		-101476		146098
35
NDT 1

Cell Formulas
Range	Formula
B3,D33,B33,D30,B30,B15,P3,I3	B3	=FORMULATEXT(B4)
B4:G13	B4	=RANDARRAY(10,6,1600,2022,1)
I4:N13	I4	=RANDARRAY(10,6,1,12,1)
P4:U13	P4	=RANDARRAY(10,6,1,31,1)
B16:G25	B16	=MAP(B4#,I4#,P4#,LAMBDA(y,m,d,NDT(y,m,d)))
B31	B31	=DATE(2022,2,30)
D31	D31	=B31
B34	B34	=NDT(2022-400,2,30)
D34	D34	=D31-B34
Dynamic array formulas.