Updating formula to not use the SEQUENCE command

[troybowman]

=SUM(N(MOD(SEQUENCE(DAY(A1),,WEEKDAY(EDATE(EOMONTH(A1,0)+1,-1))),7)=WEEKDAY(A1)))

How can I rewrite this without using the SEQUENCE command so it will work on Excel 2016

Formula will display the calendar day as first monday, second friday, etc.

A1 is the date

 

[Dave Patton]

Please explain what you want the formula to do.
An example would help.

 

[Dave Patton]

possibly

T202311a.xlsm
	A	B	C	D	E
1
2	Mon 13-Nov-23	2		Monday	0111111
3	Tue 14-Nov-23	2		Tuesday	1011111
4	Wed 15-Nov-23	3		Wednesday	1101111
5
3c

Cell Formulas
Range	Formula
B2:B4	B2	=NETWORKDAYS.INTL(A2-DAY(A2)+1,A2,VLOOKUP(TEXT(A2,"dddd"),$D$2:$E$4,2))

 

[troybowman]

Please explain what you want the formula to do.
An example would help.

Example
Today is November 13th. It is the Second Monday of November.
November 12, 2022 is the Second Monday of November.
October 12, 2021 is the Second Tuesday of October.
Etc.

 

[Fluff]

How about

=NETWORKDAYS.INTL(EOMONTH(A1,-1)+1,A1,REPLACE("1111111",WEEKDAY(A1,2),1,0))

 

[Dave Patton]

Did you try the suggestion?

T202311a.xlsm
	A	B	C	D
1
2	Mon 13-Nov-23	2	2
3	Tue 14-Nov-23	2	2
4	Wed 15-Nov-23	3	3
5
6
7	Mon 13-Nov-23		2
8	Sat 12-Nov-22		2
9	Tue 12-Oct-21		2
3c

Cell Formulas
Range	Formula
B2:B4	B2	=NETWORKDAYS.INTL(A2-DAY(A2)+1,A2,VLOOKUP(TEXT(A2,"dddd"),$E$2:$F$4,2))
C2:C4,C7:C9	C2	=NETWORKDAYS.INTL(A2-DAY(A2)+1,A2,REPLACE("1111111",MATCH(TEXT(A2,"ddd"),{"Mon","Tue","Wed","Thu","Fri","Sat","Sun"},0),1,0),$G$2)

 

[FormR]

Hi, and another option you can try:

=ROUNDUP(DAY(A1)/7,0)

 

[troybowman]

How about

=NETWORKDAYS.INTL(EOMONTH(A1,-1)+1,A1,REPLACE("1111111",WEEKDAY(A1,2),1,0))

Works like a charm!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[FormR]

Works like a charm!

Your choice of course to decide what to use, but in case you didn't try it, the simpler option in post#7 returns exactly the same result as the formula in post#5.