Return date of Wednesday's during a specific month in a row

[BURN11]

A2=1/2023, I need B2 to contain the date of the first Wednesday of 1/2023; B3 to contain the date of the second Wednesday; B4 to contain the date of the third Wednesday; B5 to contain the date of the 4th Wednesday and B6 to contain the date of the 5th Wednesday or "N/A". Continuing this formula for all the months of the year.
Any assistance would be greatly appreciated.

 

[Fluff]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B
1
2	01/01/2023	04/01/2023
3		11/01/2023
4		18/01/2023
5		25/01/2023
6		01/02/2023
7		08/02/2023
8		15/02/2023
9		22/02/2023
10		01/03/2023
11		08/03/2023
12		15/03/2023
13		22/03/2023
14		29/03/2023
15		05/04/2023
16		12/04/2023
17		19/04/2023
18		26/04/2023
19		03/05/2023
20		10/05/2023
21		17/05/2023
22		24/05/2023
23		31/05/2023
24		07/06/2023
25		14/06/2023
26		21/06/2023
27		28/06/2023
28		05/07/2023
29		12/07/2023
30		19/07/2023
31		26/07/2023
32		02/08/2023
33		09/08/2023
34		16/08/2023
35		23/08/2023
36		30/08/2023
37		06/09/2023
38		13/09/2023
39		20/09/2023
40		27/09/2023
41		04/10/2023
42		11/10/2023
43		18/10/2023
44		25/10/2023
45		01/11/2023
46		08/11/2023
47		15/11/2023
48		22/11/2023
49		29/11/2023
50		06/12/2023
51		13/12/2023
52		20/12/2023
53		27/12/2023
Master

Cell Formulas
Range	Formula
B2:B53	B2	=WORKDAY.INTL(A2-1,SEQUENCE(52),"1101111")
Dynamic array formulas.

 

[Anonymous1378]

An alternative solution; assuming the blank for the 5th week each month isn't crucial

Book23
	A	B
2	01/2023	04-Jan-23
3		11-Jan-23
4		18-Jan-23
5		25-Jan-23
6		01-Feb-23
7		08-Feb-23
8		15-Feb-23
9		22-Feb-23
10		01-Mar-23
11		08-Mar-23
12		15-Mar-23
13		22-Mar-23
14		29-Mar-23
15		05-Apr-23
16		12-Apr-23
17		19-Apr-23
18		26-Apr-23
19		03-May-23
20		10-May-23
21		17-May-23
22		24-May-23
23		31-May-23
24		07-Jun-23
25		14-Jun-23
26		21-Jun-23
27		28-Jun-23
28		05-Jul-23
29		12-Jul-23
30		19-Jul-23
31		26-Jul-23
32		02-Aug-23
33		09-Aug-23
34		16-Aug-23
35		23-Aug-23
36		30-Aug-23
37		06-Sep-23
38		13-Sep-23
39		20-Sep-23
40		27-Sep-23
41		04-Oct-23
42		11-Oct-23
43		18-Oct-23
44		25-Oct-23
45		01-Nov-23
46		08-Nov-23
47		15-Nov-23
48		22-Nov-23
49		29-Nov-23
50		06-Dec-23
51		13-Dec-23
52		20-Dec-23
53		27-Dec-23
Sheet1

Cell Formulas
Range	Formula
B2:B53	B2	=SEQUENCE((MROUND(EDATE(A2-1,12),7)+4-(MROUND(A2-1,7)+4))/7,,MROUND(A2-1,7)+4,7)
Dynamic array formulas.

 

[BURN11]

First of all, thanks for the assistance. Second, I goofed. I need the dates in a horizontal row with the 5th week either being an actual date or "N/A".
Sorry for the confusion.

 

[Fluff]

Can you post your expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Anonymous1378]

I'm taking a shot in the dark here:

Book1
	A	B	C	D	E	F
2	01/2023	04-Jan-23	11-Jan-23	18-Jan-23	25-Jan-23
3	02/2023	01-Feb-23	08-Feb-23	15-Feb-23	22-Feb-23
4	03/2023	01-Mar-23	08-Mar-23	15-Mar-23	22-Mar-23	29-Mar-23
5	04/2023	05-Apr-23	12-Apr-23	19-Apr-23	26-Apr-23
6	05/2023	03-May-23	10-May-23	17-May-23	24-May-23	31-May-23
7	06/2023	07-Jun-23	14-Jun-23	21-Jun-23	28-Jun-23
8	07/2023	05-Jul-23	12-Jul-23	19-Jul-23	26-Jul-23
9	08/2023	02-Aug-23	09-Aug-23	16-Aug-23	23-Aug-23	30-Aug-23
10	09/2023	06-Sep-23	13-Sep-23	20-Sep-23	27-Sep-23
11	10/2023	04-Oct-23	11-Oct-23	18-Oct-23	25-Oct-23
12	11/2023	01-Nov-23	08-Nov-23	15-Nov-23	22-Nov-23	29-Nov-23
13	12/2023	06-Dec-23	13-Dec-23	20-Dec-23	27-Dec-23
Sheet2

Cell Formulas
Range	Formula
A2:A13	A2	=EDATE(DATE(2023,1,1),SEQUENCE(12,,0))
B13:E13,B12:F12,B10:E11,B9:F9,B7:E8,B6:F6,B5:E5,B4:F4,B2:E3	B2	=SEQUENCE(1,(MROUND(EOMONTH(A2,0),7)+4-(MROUND(A2-1,7)+4))/7,MROUND(A2-1,7)+4,7)
Dynamic array formulas.

 

[lrobbo314]

Haha, that was how I originally set it up.

I made a LAMBDA out of it. The argument 'DOW' is a number from 1 to 7 where 1 is Monday and 7 is Sunday.

DOM
	A	B	C	D	E	F
1		1	2	3	4	5
2	1/2023	1/4/2023	1/11/2023	1/18/2023	1/25/2023	#N/A
3	2/2023	2/1/2023	2/8/2023	2/15/2023	2/22/2023	#N/A
4	3/2023	3/1/2023	3/8/2023	3/15/2023	3/22/2023	3/29/2023
5	4/2023	4/5/2023	4/12/2023	4/19/2023	4/26/2023	#N/A
6	5/2023	5/3/2023	5/10/2023	5/17/2023	5/24/2023	5/31/2023
7	6/2023	6/7/2023	6/14/2023	6/21/2023	6/28/2023	#N/A
8	7/2023	7/5/2023	7/12/2023	7/19/2023	7/26/2023	#N/A
9	8/2023	8/2/2023	8/9/2023	8/16/2023	8/23/2023	8/30/2023
10	9/2023	9/6/2023	9/13/2023	9/20/2023	9/27/2023	#N/A
11	10/2023	10/4/2023	10/11/2023	10/18/2023	10/25/2023	#N/A
12	11/2023	11/1/2023	11/8/2023	11/15/2023	11/22/2023	11/29/2023
13	12/2023	12/6/2023	12/13/2023	12/20/2023	12/27/2023	#N/A
Sheet12

Cell Formulas
Range	Formula
B1:F1	B1	=SEQUENCE(,5)
B2:F13	B2	=Nth_DAY_OF_MONTH($A2,3,B$1)
A3:A13	A3	=EOMONTH(A2,0)+1
Dynamic array formulas.

LAMBDA Funciton

=LAMBDA(start_date,dow,nth_day,
    LET(
        dt,WORKDAY.INTL(
            DATE(YEAR(start_date),
            MONTH(start_date),0),
            nth_day,
            REPLACE("1111111",dow,1,"0")
            ),
        IF(MONTH(dt)=MONTH(start_date),dt,NA())
    )
)

 

[Tetra201]

Here is a single-cell spill formula for you to try:

=LET(d,WORKDAY.INTL(EOMONTH(A2,-1),1,"1101111"),SCAN(d-7,SCAN(d-7,MAKEARRAY(12,5,LAMBDA(r,c,MOD(r-2+MONTH(d),12)+1)),LAMBDA(a,b,a+(MONTH(a+7)=b)*7)),LAMBDA(x,y,IF(x<>y,y,"N/A"))))

 

[BURN11]

WEEKLY CHECKLIST FOR INSPECTION OF EYEWASH Read ALL instructions prior to inspection 1. Ensure that the path to the eyewash is not obstructed, accessible within 10 seconds or 55 feet, well lit and highly visible. 2. Verify that nozzle caps are in place to prevent contamination and that the nozzles, nozzle caps, and bowl/sink are clean and sanitary. 3. Where feasible, place a catch pan or bucket under the unit if a plumbed drain is not available. 4. Actuate valve to full open position. Water must flow within 1 second. 5. Verify that nozzle caps come off when the eyewash or drench hose is activated. 6. Verify water continues to flow until manually turned off and can be used without requiring the use of the operator’s hands. 7. Look at the flow pattern. It should provide a gentle (30-90 PSI) non-injurious flow. If a dual-stream eyewash, both streams should rise to equal height in a pattern that will flush both eyes simultaneously. Height of fluid flow pattern is 33 - 53 inches from the surface the user stands on and at least 0.6 inches from the wall. 8. Run water for 1-2 minutes to see that there is proper flow and that running water does not appear dirty or cloudy. 9. Report problems to safety and FM and/or start work task order if our facility.
	Month	Date and initial below to show above items have been checked	Note Problems Make sure to report
	Jan-23	Week 1	Week 2	Week 3	Week 4	Week 5 or NA
	Feb-23	Week 1	Week 2	Week 3	Week 4	Week 5 or NA
	Mar-23	Week 1	Week 2	Week 3	Week 4	Week 5 or NA
	Apr-23	Week 1	Week 2	Week 3	Week 4	Week 5 or NA
	May-23	Week 1	Week 2	Week 3	Week 4	Week 5 or NA
	Jun-23	Week 1	Week 2	Week 3	Week 4	Week 5 or NA
	Jul-23	Week 1	Week 2	Week 3	Week 4	Week 5 or NA
	23-Aug	Week 1	Week 2	Week 3	Week 4	Week 5 or NA
	23-Sep	Week 1	Week 2	Week 3	Week 4	Week 5 or NA
	23-Oct	Week 1	Week 2	Week 3	Week 4	Week 5 or NA
	23-Nov	Week 1	Week 2	Week 3	Week 4	Week 5 or NA
	23-Dec	Week 1	Week 2	Week 3	Week 4	Week 5 or NA

 

[Dave Patton]

N.B. Your Month column must be an actual date Jan 1 2023 etc.
I included an example. You can format it to your preferences.
If you want to add text or check marks, convert the entire range to value.
The previous posts are excellent suggestions but I tried to make the solution less complex.

You can copy the post to a clean sheet.
Click on the icon below the f(x) in the heading, move to your sheet and select cell and paste.

LAMBDA.xlsm
	C	D	E	F	G	H
22	Date	1	2	3	4	5
23	01-Jan-23	Wed 4-Jan	Wed 11-Jan	Wed 18-Jan	Wed 25-Jan	N/A
24	01-Feb-23	Wed 1-Feb	Wed 8-Feb	Wed 15-Feb	Wed 22-Feb	N/A
25	01-Mar-23	Wed 1-Mar	Wed 8-Mar	Wed 15-Mar	Wed 22-Mar	Wed 29-Mar
26	01-Apr-23	Wed 5-Apr	Wed 12-Apr	Wed 19-Apr	Wed 26-Apr	N/A
27	01-May-23	Wed 3-May	Wed 10-May	Wed 17-May	Wed 24-May	Wed 31-May
28	01-Jun-23	Wed 7-Jun	Wed 14-Jun	Wed 21-Jun	Wed 28-Jun	N/A
29	01-Jul-23	Wed 5-Jul	Wed 12-Jul	Wed 19-Jul	Wed 26-Jul	N/A
30	01-Aug-23	Wed 2-Aug	Wed 9-Aug	Wed 16-Aug	Wed 23-Aug	Wed 30-Aug
31	01-Sep-23	Wed 6-Sep	Wed 13-Sep	Wed 20-Sep	Wed 27-Sep	N/A
32	01-Oct-23	Wed 4-Oct	Wed 11-Oct	Wed 18-Oct	Wed 25-Oct	N/A
33	01-Nov-23	Wed 1-Nov	Wed 8-Nov	Wed 15-Nov	Wed 22-Nov	Wed 29-Nov
34	01-Dec-23	Wed 6-Dec	Wed 13-Dec	Wed 20-Dec	Wed 27-Dec	N/A
35
9d

Cell Formulas
Range	Formula
D23:H34	D23	=LET(x,WORKDAY.INTL($C23-1,D$22,"1101111"),IF(MONTH(x)=MONTH(C23),x,"N/A"))