Formula for the nth day in the NEXT month from a specific date

[JosieJo89]

HI All

I have been looking online and cannot find a formula to help with my issue. I am a community Mental Health Nurse who coordinates out clients Depot injections.

some require them on the nth day of the month. for example Mr A had his depot on 18/06/2020 and has it every 3rd Thursday of the month.

I need to be able to calculate from that date when the 3rd Thursday of the NEXT month will fall. My colleagues are not computer savvy and need to put in the date given and then be given the next date it is due so that they can give out the correct appointment date to the client.

I hope that makes sense to someone and they can help me out.

thank you

 

[Darren Bartrup]

Probably a smarter way to do this, but this is the formula I came up with.

=IF(WEEKDAY(EOMONTH(A1,0)+1+(2*7),3)>WEEKDAY(A1,3),EOMONTH(A1,0)+1+(2*7)+7,EOMONTH(A1,0)+1+(2*7))-WEEKDAY(IF(WEEKDAY(EOMONTH(A1,0)+1+(2*7),3)>WEEKDAY(A1,3),EOMONTH(A1,0)+1+(2*7)+7,EOMONTH(A1,0)+1+(2*7)),3)+WEEKDAY(A1,3)

I'll break it down into separate formula:
Your date in cell A1.

Calculate the start of the following month in A2: =EOMONTH(A1,0)+1
Calculate the third week of the month in A3: =A2+(2*7)
Adjust the third week - if the weekday is after the target weekday we need to add another 7 days in A4: =IF(WEEKDAY(A3,3)>WEEKDAY(A1,3),A3+7,A3)
Calculate the Monday of that week in A5: =A4-WEEKDAY(A4,3)
Calculate the correct day of the week in A6: =A5+WEEKDAY(A1,3)

 

[Dave Patton]

Copy the formula across as many times as required

T202007b.xlsm
	C	D	E	F
1
2	Mon 29-Jun-20	Thu 16-Jul-20	Thu 20-Aug-20	Thu 17-Sep-20
3
3bb

Cell Formulas
Range	Formula
D2:F2	D2	=WORKDAY.INTL(EOMONTH(C2,0),3,"1110111")

 

[Dave Patton]

This post shows a flexible approach.
The 1 st example is consistent with your original post of 3rd Thursday of next month.
You can change the 3 in B7 to 1, 2, 3, 4, or possibly 5. If you specify 5 and there are not 5 of those weekdays in the next month, it will show the day in the subsequent month.
The Thursday can be changed to Monday, Tuesday, ..... Sunday.
The second example shows the 2nd Friday of the next month.

DOWI is a named array see Name Manager refers to: ={"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"}

T202007b.xlsm
	B	C	D
7	3	Thursday	of Next month
8	15-Jul-20	Thu 20-Aug-20
9
10
3bb

Cell Formulas
Range	Formula
B8	B8	=TODAY()
C8	C8	=WORKDAY.INTL(EOMONTH($B$8,0),$B$7,REPLACE("1111111",MATCH($C$7,DOWI,0),1,0))

T202007b.xlsm
	B	C	D
7	2	Friday	of Next month
8	15-Jul-20	Fri 14-Aug-20
9
10
3bb

Cell Formulas
Range	Formula
B8	B8	=TODAY()
C8	C8	=WORKDAY.INTL(EOMONTH($B$8,0),$B$7,REPLACE("1111111",MATCH($C$7,DOWI,0),1,0))

 

[Dave Patton]

This example is more flexible. It uses 2 Named arrays:
DOWL is a named array see Name Manager refers to: ={"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"}
DOWS is a named array see Name Manager refers to: ={"Mon","Tue","Wed","Thu","Fri","Sat","Sun"}

T202007b.xlsm
	A	B	C	D
1	Instance Next Month	DOW	Date	Result
2	3	Thursday	15-Jul-20	Thu 20-Aug-20
3	2	Fri	05-May-20	Fri 12-Jun-20
4	5	Sun	31-Jul-20	Sun 30-Aug-20
5	1	Mon	16-Jul-20	Mon 03-Aug-20
6
3bb

Cell Formulas
Range	Formula
D2:D5	D2	=WORKDAY.INTL(EOMONTH($C2,0),$A2,REPLACE("1111111",MATCH($B2,IF(LEN(B2)>3,DOWL,DOWS),0),1,0))
C5	C5	=TODAY()

 

[JosieJo89]

thank you for this.

I will try it in my spread sheet. My colleagues will save time giving out appointments to already disgruntled clients. (wouldn't you be if you have just had an injection in the ****! I know I would)

 

[Dave Patton]

The formula is flexible; hopefully, it helps you.
The formula can be more concise; the suggestion below allows the full day of the week like Thursday or Thu.
The formula is also shown without the named array.

T202007b.xlsm
	A	B	C	D
2	3	Thursday	Wed 15-Jul-20	Thu 30-Jul-20
3	2	Fri	17-Jul-20	Fri 31-Jul-20
3bbb

Cell Formulas
Range	Formula
D2	D2	=WORKDAY.INTL(C2,$A2,REPLACE("1111111",MATCH(LEFT(B2,3),DOWS,0),1,0))
C3	C3	=TODAY()
D3	D3	=WORKDAY.INTL(C3,$A3,REPLACE("1111111",MATCH(LEFT(B3,3),{"Mon","Tue","Wed","Thu","Fri","Sat","Sun"},0),1,0))

T202007b.xlsm
	A	B	C	D
9			15-Jul-20	Thu 20-Aug-20
3bb

Cell Formulas
Range	Formula
D9	D9	=WORKDAY.INTL(EOMONTH($C9,0),3,"1110111")