Nth Date Question by a Noob

[MadDad75]

This is my very first post. I've tried to search answer on the 'internet' to no avail. But a friend had suggested mrexcel.com forum. I've had a look around and couldn't find any answers (bit of a Noob at this, sorry if the question has already been asked somewhere.)

I'm trying 'badly' to find a formula to calculate what the Nth is of a specific date.

ie. So today is Sunday 21st May 2023, what formula could I use for it to tell that it's the 3rd Sunday of the Month.

I'm not looking for a formula that tells me what the 3rd Sunday of a particular month is. I would also like it that it'll tell me what the Nth is of any date that I put in.

Thanks to anyone who answers.

 

[MadDad75]

Found it

=CEILING((DAY(A1-WEEKDAY(A1,2)+5))/7,1

 

[Dave Patton]

Did you check your formula on a variety of dates?
I am not sure what your exact requirements are.
You can try the following with your data.

Dates_2023.xlsm
	A	B	C
1	Sun 21-May-23	3	3
2	Mon 29-May-23	5	5
3	Wed 31-May-23	5	5th Wed
4	Mon 01-May-23	1	1st Mon
5
3e

Cell Formulas
Range	Formula
B1	B1	=NETWORKDAYS.INTL(A1-DAY(A1)+1,A1,"1111110")
C1:C2	C1	=NETWORKDAYS.INTL(A1-DAY(A1)+1,A1,REPLACE("1111111",WEEKDAY(A1,2),1,"0"))
B2,B4	B2	=NETWORKDAYS.INTL(A2-DAY(A2)+1,A2,"0111111")
B3	B3	=NETWORKDAYS.INTL(A3-DAY(A3)+1,A3,"1101111")
C3:C4	C3	=LET(n,NETWORKDAYS.INTL(A3-DAY(A3)+1,A3,REPLACE("1111111",WEEKDAY(A3,2),1,"0")),n&LOOKUP(n,{0,1,2,3,4},{"th","st","nd","rd","th"}))&TEXT(A3," ddd")

 

[Tetra201]

Try using just

=CEILING(DAY(A1)/7,1)

 

[MadDad75]

Try using just

=CEILING(DAY(A1)/7,1)

Thanks

 

[MadDad75]

Did you check your formula on a variety of dates?
I am not sure what your exact requirements are.
You can try the following with your data.

Dates_2023.xlsm
	A	B	C
1	Sun 21-May-23	3	3
2	Mon 29-May-23	5	5
3	Wed 31-May-23	5	5th Wed
4	Mon 01-May-23	1	1st Mon
5
3e

Cell Formulas
Range	Formula
B1	B1	=NETWORKDAYS.INTL(A1-DAY(A1)+1,A1,"1111110")
C1:C2	C1	=NETWORKDAYS.INTL(A1-DAY(A1)+1,A1,REPLACE("1111111",WEEKDAY(A1,2),1,"0"))
B2,B4	B2	=NETWORKDAYS.INTL(A2-DAY(A2)+1,A2,"0111111")
B3	B3	=NETWORKDAYS.INTL(A3-DAY(A3)+1,A3,"1101111")
C3:C4	C3	=LET(n,NETWORKDAYS.INTL(A3-DAY(A3)+1,A3,REPLACE("1111111",WEEKDAY(A3,2),1,"0")),n&LOOKUP(n,{0,1,2,3,4},{"th","st","nd","rd","th"}))&TEXT(A3," ddd")

Thanks