NTHDAYOFMONTH

[lrobbo314]

NTHDAYOFMONTH takes 4 arguments which can be single cell references or ranges. The function will return the nth day of the month for a given year, month, day of week, and nth day of month.

=LAMBDA(year,month,day_index,nth_day,
    MAP(year,month,day_index,nth_day,
        LAMBDA(y,m,d,n,
            WORKDAY.INTL(
                DATE(y,m,0),
                n,
                REPLACE("111111",d,0,"0")
            )
        )
    )
)

COMPSTAT Template.xltx
	A	B	C	D	E	F
1	Year	Month	Day Index	Nth Day	FX	Day
2	2022	11	1	1	11/7/2022	Mon
3	2022	11	2	1	11/1/2022	Tue
4	2022	11	3	1	11/2/2022	Wed
5	2022	11	4	1	11/3/2022	Thu
6	2022	11	5	1	11/4/2022	Fri
7	2022	11	6	1	11/5/2022	Sat
8	2022	11	7	1	11/6/2022	Sun
9	2022	11	1	2	11/14/2022	Mon
10	2022	11	2	2	11/8/2022	Tue
11	2022	11	3	2	11/9/2022	Wed
12	2022	11	4	2	11/10/2022	Thu
13	2022	11	5	2	11/11/2022	Fri
14	2022	11	6	2	11/12/2022	Sat
15	2022	11	7	2	11/13/2022	Sun
16	2022	11	1	3	11/21/2022	Mon
17	2022	11	2	3	11/15/2022	Tue
18	2022	11	3	3	11/16/2022	Wed
19	2022	11	4	3	11/17/2022	Thu
20	2022	11	5	3	11/18/2022	Fri
21	2022	11	6	3	11/19/2022	Sat
22	2022	11	7	3	11/20/2022	Sun
23	2022	11	1	4	11/28/2022	Mon
24	2022	11	2	4	11/22/2022	Tue
25	2022	11	3	4	11/23/2022	Wed
26	2022	11	4	4	11/24/2022	Thu
27	2022	11	5	4	11/25/2022	Fri
28	2022	11	6	4	11/26/2022	Sat
29	2022	11	7	4	11/27/2022	Sun
Data

Cell Formulas
Range	Formula
C2:C29	C2	=MOD(SEQUENCE(28,,0),7)+1
D2:D29	D2	=INT(SEQUENCE(28,,0)/7)+1
E2:E29	E2	=NTHDAYINMONTH(A2:A29,B2:B29,C2#,D2#)
F2:F29	F2	=TEXT(E2#,"ddd")
Dynamic array formulas.