Depending on Start date / formula for 1st Monday of month (of 1st FULL week)

[TarkTeam4]

Who's up for this challenge?

based on a Thursday start date...
I need ONE formula to always pick the next FIRST Monday of a month (that falls on a FULL week***)...whether it be this month or next.

Example:
if start date is Thursday November 3rd, 2022, the formula would choose Monday, November 7th, 2022.
However, if start date is Thursday, November 10th, 2022 (or 17th, or 24th), then the formula would choose Monday, December 5th, 2022.
I need ONE formula, that will factor either circumstance.

*** Note: Nov. 1st is on a Tuesday ... the first FULL week in November is the following week (11/6 - 11/12).
The same for Dec. 1st falls on a Thursday, therefore the first FULL week in December is the following week (12-4 - 12/10)
I need the Mondays that fall within the first FULL week.

 

[kevin9999]

There must be a simpler way of doing it than this, but...

Book1
	A	B
1	Start Date	Monday 1st full week
2	Tuesday, 1 November 2022	Monday, 7 November 2022
3	Thursday, 3 November 2022	Monday, 7 November 2022
4	Thursday, 10 November 2022	Monday, 5 December 2022
5	Thursday, 17 November 2022	Monday, 5 December 2022
6	Thursday, 24 November 2022	Monday, 5 December 2022
7	Thursday, 1 December 2022	Monday, 5 December 2022
8	Friday, 9 December 2022	Monday, 2 January 2023
Sheet1

Cell Formulas
Range	Formula
B2:B8	B2	=IF(DAY(A2)>6,EOMONTH(A2,0)+MATCH(2,WEEKDAY(EOMONTH(A2,0)+{1,2,3,4,5,6,7}),0),A2+MOD(8-WEEKDAY(A2,2),7))

 

[StephenCrump]

Another way:

	A	B	C	D	E	F
1		1	Monday 1st full wk			TEST
2		Mon 1 Aug 2022	Mon 8 Aug 2022		M	Mon 1 Aug 2022
3		Tue 2 Aug 2022	Mon 8 Aug 2022		T	Tue 1 Nov 2022
4		Wed 3 Aug 2022	Mon 8 Aug 2022		W	Wed 1 Jun 2022
5		Thu 4 Aug 2022	Mon 8 Aug 2022		T	Thu 1 Sep 2022
6		Fri 5 Aug 2022	Mon 8 Aug 2022		F	Fri 1 Jul 2022
7		Sat 6 Aug 2022	Mon 8 Aug 2022		S	Sat 1 Oct 2022
8		Sun 7 Aug 2022	Mon 8 Aug 2022		S	Sun 1 May 2022
9		Mon 8 Aug 2022	Mon 8 Aug 2022
10		Tue 9 Aug 2022	Mon 5 Sep 2022
11		Wed 10 Aug 2022	Mon 5 Sep 2022
12		Thu 11 Aug 2022	Mon 5 Sep 2022
13		Fri 12 Aug 2022	Mon 5 Sep 2022
14		Sat 13 Aug 2022	Mon 5 Sep 2022
15		Sun 14 Aug 2022	Mon 5 Sep 2022
16		Mon 15 Aug 2022	Mon 5 Sep 2022
17		Tue 16 Aug 2022	Mon 5 Sep 2022
18		Wed 17 Aug 2022	Mon 5 Sep 2022
19		Thu 18 Aug 2022	Mon 5 Sep 2022
20		Fri 19 Aug 2022	Mon 5 Sep 2022
21		Sat 20 Aug 2022	Mon 5 Sep 2022
22		Sun 21 Aug 2022	Mon 5 Sep 2022
23		Mon 22 Aug 2022	Mon 5 Sep 2022
24		Tue 23 Aug 2022	Mon 5 Sep 2022
25		Wed 24 Aug 2022	Mon 5 Sep 2022
26		Thu 25 Aug 2022	Mon 5 Sep 2022
27		Fri 26 Aug 2022	Mon 5 Sep 2022
28		Sat 27 Aug 2022	Mon 5 Sep 2022
29		Sun 28 Aug 2022	Mon 5 Sep 2022
30		Mon 29 Aug 2022	Mon 5 Sep 2022
31		Tue 30 Aug 2022	Mon 5 Sep 2022
32		Wed 31 Aug 2022	Mon 5 Sep 2022
33		Thu 1 Sep 2022	Mon 5 Sep 2022
34		Fri 2 Sep 2022	Mon 5 Sep 2022
35		Sat 3 Sep 2022	Mon 5 Sep 2022
36		Sun 4 Sep 2022	Mon 5 Sep 2022
37		Mon 5 Sep 2022	Mon 5 Sep 2022
38		Tue 6 Sep 2022	Mon 3 Oct 2022
39		Wed 7 Sep 2022	Mon 3 Oct 2022
40		Thu 8 Sep 2022	Mon 3 Oct 2022
41		Fri 9 Sep 2022	Mon 3 Oct 2022
42
Sheet1

Cell Formulas
Range	Formula
B2:B41	B2	=SEQUENCE(40,,INDEX(Test,B1))
C2:C41	C2	=LET(d,8+EOMONTH(B2,-(DAY(B2)-WEEKDAY(B2,12)<2)),1+d-WEEKDAY(d-1))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Test	=Sheet1!$F$2:$F$8	B2

 

[kevin9999]

Another way:

	A	B	C	D	E	F
1		1	Monday 1st full wk			TEST
2		Mon 1 Aug 2022	Mon 8 Aug 2022		M	Mon 1 Aug 2022
3		Tue 2 Aug 2022	Mon 8 Aug 2022		T	Tue 1 Nov 2022
4		Wed 3 Aug 2022	Mon 8 Aug 2022		W	Wed 1 Jun 2022
5		Thu 4 Aug 2022	Mon 8 Aug 2022		T	Thu 1 Sep 2022
6		Fri 5 Aug 2022	Mon 8 Aug 2022		F	Fri 1 Jul 2022
7		Sat 6 Aug 2022	Mon 8 Aug 2022		S	Sat 1 Oct 2022
8		Sun 7 Aug 2022	Mon 8 Aug 2022		S	Sun 1 May 2022
9		Mon 8 Aug 2022	Mon 8 Aug 2022
10		Tue 9 Aug 2022	Mon 5 Sep 2022
11		Wed 10 Aug 2022	Mon 5 Sep 2022
12		Thu 11 Aug 2022	Mon 5 Sep 2022
13		Fri 12 Aug 2022	Mon 5 Sep 2022
14		Sat 13 Aug 2022	Mon 5 Sep 2022
15		Sun 14 Aug 2022	Mon 5 Sep 2022
16		Mon 15 Aug 2022	Mon 5 Sep 2022
17		Tue 16 Aug 2022	Mon 5 Sep 2022
18		Wed 17 Aug 2022	Mon 5 Sep 2022
19		Thu 18 Aug 2022	Mon 5 Sep 2022
20		Fri 19 Aug 2022	Mon 5 Sep 2022
21		Sat 20 Aug 2022	Mon 5 Sep 2022
22		Sun 21 Aug 2022	Mon 5 Sep 2022
23		Mon 22 Aug 2022	Mon 5 Sep 2022
24		Tue 23 Aug 2022	Mon 5 Sep 2022
25		Wed 24 Aug 2022	Mon 5 Sep 2022
26		Thu 25 Aug 2022	Mon 5 Sep 2022
27		Fri 26 Aug 2022	Mon 5 Sep 2022
28		Sat 27 Aug 2022	Mon 5 Sep 2022
29		Sun 28 Aug 2022	Mon 5 Sep 2022
30		Mon 29 Aug 2022	Mon 5 Sep 2022
31		Tue 30 Aug 2022	Mon 5 Sep 2022
32		Wed 31 Aug 2022	Mon 5 Sep 2022
33		Thu 1 Sep 2022	Mon 5 Sep 2022
34		Fri 2 Sep 2022	Mon 5 Sep 2022
35		Sat 3 Sep 2022	Mon 5 Sep 2022
36		Sun 4 Sep 2022	Mon 5 Sep 2022
37		Mon 5 Sep 2022	Mon 5 Sep 2022
38		Tue 6 Sep 2022	Mon 3 Oct 2022
39		Wed 7 Sep 2022	Mon 3 Oct 2022
40		Thu 8 Sep 2022	Mon 3 Oct 2022
41		Fri 9 Sep 2022	Mon 3 Oct 2022
42
Sheet1

Cell Formulas
Range	Formula
B2:B41	B2	=SEQUENCE(40,,INDEX(Test,B1))
C2:C41	C2	=LET(d,8+EOMONTH(B2,-(DAY(B2)-WEEKDAY(B2,12)<2)),1+d-WEEKDAY(d-1))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Test	=Sheet1!$F$2:$F$8	B2

That's much better @StephenCrump

 

[TarkTeam4]

Thank you @StephenCrump and @kevin9999 !!!
I hope it wasn't too difficult trying to come up with a formula. I've tried several ways and it always picked up the next Monday, not the next FULL Monday.

I ended going with the formula @StephenCrump came up with.

Thanks again.

 

[TarkTeam4]

Oops, my mistake, I meant I went with @kevin9999 formula.

 

[kevin9999]

Oops, my mistake, I meant I went with @kevin9999 formula.

Glad we could help.

 

[StephenCrump]

The two formulae occasionally produce different results, e.g. testing using only Thursdays (your original question):

	A	B	C	D	E	F	G
1		3	Monday 1st full wk				TEST
2		Thu 2 Jun 2022	Mon 6 Jun 2022	Mon 6 Jun 2022		M	Thu 4 Aug 2022
3		Thu 9 Jun 2022	Mon 4 Jul 2022	Mon 4 Jul 2022		T	Thu 3 Nov 2022
4		Thu 16 Jun 2022	Mon 4 Jul 2022	Mon 4 Jul 2022		W	Thu 2 Jun 2022
5		Thu 23 Jun 2022	Mon 4 Jul 2022	Mon 4 Jul 2022		T	Thu 1 Sep 2022
6		Thu 30 Jun 2022	Mon 4 Jul 2022	Mon 4 Jul 2022		F	Thu 7 Jul 2022
7		Thu 7 Jul 2022	Mon 8 Aug 2022	Mon 1 Aug 2022		S	Thu 6 Oct 2022
8		Thu 14 Jul 2022	Mon 8 Aug 2022	Mon 1 Aug 2022		S	Thu 5 May 2022
9		Thu 21 Jul 2022	Mon 8 Aug 2022	Mon 1 Aug 2022
10		Thu 28 Jul 2022	Mon 8 Aug 2022	Mon 1 Aug 2022
11		Thu 4 Aug 2022	Mon 8 Aug 2022	Mon 8 Aug 2022
12		Thu 11 Aug 2022	Mon 5 Sep 2022	Mon 5 Sep 2022
13		Thu 18 Aug 2022	Mon 5 Sep 2022	Mon 5 Sep 2022
14		Thu 25 Aug 2022	Mon 5 Sep 2022	Mon 5 Sep 2022
15		Thu 1 Sep 2022	Mon 5 Sep 2022	Mon 5 Sep 2022
16		Thu 8 Sep 2022	Mon 3 Oct 2022	Mon 3 Oct 2022
17		Thu 15 Sep 2022	Mon 3 Oct 2022	Mon 3 Oct 2022
18		Thu 22 Sep 2022	Mon 3 Oct 2022	Mon 3 Oct 2022
19		Thu 29 Sep 2022	Mon 3 Oct 2022	Mon 3 Oct 2022
20		Thu 6 Oct 2022	Mon 7 Nov 2022	Mon 10 Oct 2022
21		Thu 13 Oct 2022	Mon 7 Nov 2022	Mon 7 Nov 2022
22
Sheet1

Cell Formulas
Range	Formula
B2:B21	B2	=SEQUENCE(20,,INDEX(Test,B1),7)
C2:C21	C2	=LET(d,8+EOMONTH(B2,-(DAY(B2)-WEEKDAY(B2,12)<2)),1+d-WEEKDAY(d-1))
D2:D21	D2	=IF(DAY(B2)>6,EOMONTH(B2,0)+MATCH(2,WEEKDAY(EOMONTH(B2,0)+{1,2,3,4,5,6,7}),0),B2+MOD(8-WEEKDAY(B2,2),7))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Test	=Sheet1!$G$2:$G$8	B2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:D21	Expression	=D2<>C2	text	NO

 

[Dave Patton]

One more for variety

T202209a.xlsm
	A	B	C	D	E	F
1		3	Monday 1st full wk			Equal
2		Wed 01-Jun-22	Mon 06-Jun-22	Mon 06-Jun-22	Mon 06-Jun-22	TRUE
3		Wed 08-Jun-22	Mon 04-Jul-22	Mon 04-Jul-22	Mon 04-Jul-22	TRUE
4		Wed 15-Jun-22	Mon 04-Jul-22	Mon 04-Jul-22	Mon 04-Jul-22	TRUE
5		Wed 22-Jun-22	Mon 04-Jul-22	Mon 04-Jul-22	Mon 04-Jul-22	TRUE
6		Wed 29-Jun-22	Mon 04-Jul-22	Mon 04-Jul-22	Mon 04-Jul-22	TRUE
7		Wed 06-Jul-22	Mon 08-Aug-22	Mon 11-Jul-22	Mon 01-Aug-22	FALSE
8		Wed 13-Jul-22	Mon 08-Aug-22	Mon 01-Aug-22	Mon 01-Aug-22	FALSE
9		Wed 20-Jul-22	Mon 08-Aug-22	Mon 01-Aug-22	Mon 01-Aug-22	FALSE
10		Wed 27-Jul-22	Mon 08-Aug-22	Mon 01-Aug-22	Mon 01-Aug-22	FALSE
11		Wed 03-Aug-22	Mon 08-Aug-22	Mon 08-Aug-22	Mon 05-Sep-22	FALSE
12		Wed 10-Aug-22	Mon 05-Sep-22	Mon 05-Sep-22	Mon 05-Sep-22	TRUE
13		Wed 17-Aug-22	Mon 05-Sep-22	Mon 05-Sep-22	Mon 05-Sep-22	TRUE
14		Wed 24-Aug-22	Mon 05-Sep-22	Mon 05-Sep-22	Mon 05-Sep-22	TRUE
15		Wed 31-Aug-22	Mon 05-Sep-22	Mon 05-Sep-22	Mon 05-Sep-22	TRUE
16		Wed 07-Sep-22	Mon 03-Oct-22	Mon 03-Oct-22	Mon 03-Oct-22	TRUE
17		Wed 14-Sep-22	Mon 03-Oct-22	Mon 03-Oct-22	Mon 03-Oct-22	TRUE
18		Wed 21-Sep-22	Mon 03-Oct-22	Mon 03-Oct-22	Mon 03-Oct-22	TRUE
19		Wed 28-Sep-22	Mon 03-Oct-22	Mon 03-Oct-22	Mon 03-Oct-22	TRUE
20		Wed 05-Oct-22	Mon 07-Nov-22	Mon 10-Oct-22	Mon 07-Nov-22	FALSE
21		Wed 12-Oct-22	Mon 07-Nov-22	Mon 07-Nov-22	Mon 07-Nov-22	TRUE
22		Tue 01-Nov-22	Mon 07-Nov-22	Mon 07-Nov-22	Mon 07-Nov-22	TRUE
23		Wed 02-Nov-22	Mon 07-Nov-22	Mon 07-Nov-22	Mon 07-Nov-22	TRUE
24		Thu 03-Nov-22	Mon 07-Nov-22	Mon 07-Nov-22	Mon 07-Nov-22	TRUE
25
5a

Cell Formulas
Range	Formula
C2:C24	C2	=LET(d,8+EOMONTH(B2,-(DAY(B2)-WEEKDAY(B2,12)<2)),1+d-WEEKDAY(d-1))
D2:D24	D2	=IF(DAY(B2)>6,EOMONTH(B2,0)+MATCH(2,WEEKDAY(EOMONTH(B2,0)+{1,2,3,4,5,6,7}),0),B2+MOD(8-WEEKDAY(B2,2),7))
E2:E24	E2	=LET(x,WORKDAY.INTL(B2-1,1,"0111111"),IF(DAY(x)<8,x,WORKDAY.INTL(EOMONTH(B2,0),1,"0111111")))
F2:F24	F2	=COUNTIF(C2:E2,C2)=3

 

[Dave Patton]

I revised my suggestion. I posted it just so I can clear the example from this computer.

T202209a.xlsm
	A	B			E
1					Monday 1st full wk
2		Wed 01-Jun-22			Mon 06-Jun-22
3		Sat 11-Jun-22			Mon 04-Jul-22
4		Tue 21-Jun-22			Mon 04-Jul-22
5		Fri 01-Jul-22			Mon 04-Jul-22
6		Mon 11-Jul-22			Mon 08-Aug-22
7		Thu 21-Jul-22			Mon 08-Aug-22
8		Sun 31-Jul-22			Mon 08-Aug-22
9		Wed 10-Aug-22			Mon 05-Sep-22
10		Sat 20-Aug-22			Mon 05-Sep-22
11		Tue 30-Aug-22			Mon 05-Sep-22
12		Fri 09-Sep-22			Mon 03-Oct-22
13		Mon 19-Sep-22			Mon 03-Oct-22
14		Thu 29-Sep-22			Mon 03-Oct-22
15		Sun 09-Oct-22			Mon 07-Nov-22
16		Wed 19-Oct-22			Mon 07-Nov-22
17		Tue 01-Nov-22			Mon 07-Nov-22
18		Thu 03-Nov-22			Mon 07-Nov-22
19		Thu 10-Nov-22			Mon 05-Dec-22
20		Fri 18-Nov-22			Mon 05-Dec-22
21		Thu 01-Dec-22			Mon 05-Dec-22
22		Thu 08-Dec-22			Mon 02-Jan-23
23		Sun 18-Dec-22			Mon 02-Jan-23
24		Wed 28-Dec-22			Mon 02-Jan-23
25		Sat 07-Jan-23			Mon 06-Feb-23
26		Tue 17-Jan-23			Mon 06-Feb-23
27
5a

Cell Formulas
Range	Formula
E2:E26	E2	=LET(f,WORKDAY.INTL(B2-DAY(B2),1,"1111110")+1,IF(WORKDAY.INTL(B2,1,"0111111")>f,WORKDAY.INTL(EOMONTH(B2,0),1,"1111110")+1,f))