automatic show start day and last day of each week when select week1, week2, ...

[tendosai]

I have table like below. however, doing the date manually is tough for every month. the date is shown from the start day of the week till last day of week (Mon to Sun). Example: below is week 1 so it will show like below (Mon and Tue not show any date because it was in Jan).

Week1
Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
		1-Feb-23	2-Feb-23	3-Feb-23	4-Feb-23	5-Feb-23

so when change to Week2 it shows like below

Week2
Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
6-Feb-23	7-Feb-23	8-Feb-23	9-Feb-23	10-Feb-23	11-Feb-23	12-Feb-23

same for week3 till week5 (base on our calendar)
i try to figure out using formular but it started to get complicated and going nowhere. it would be the best if everyone can help with VBA.

 

[Dave Patton]

You do not say how you specify the Date such as February 1 2023.
You can provide more information or try the following.

Calendar Examples 2.xlsm
	A	B	C	D	E	F	G
1	Year	2023
2	Month	Feb		February 2023
3
4	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
5			1-Feb-23	2-Feb-23	3-Feb-23	4-Feb-23	5-Feb-23
6	6-Feb-23	7-Feb-23	8-Feb-23	9-Feb-23	10-Feb-23	11-Feb-23	12-Feb-23
7	13-Feb-23	14-Feb-23	15-Feb-23	16-Feb-23	17-Feb-23	18-Feb-23	19-Feb-23
8	20-Feb-23	21-Feb-23	22-Feb-23	23-Feb-23	24-Feb-23	25-Feb-23	26-Feb-23
9	27-Feb-23	28-Feb-23
10
ISO Standard 8601

Cell Formulas
Range	Formula
D2	D2	=DATE(B1,MONTH(--(1&B2)),1)
A5:G10	A5	=LET(dt,SEQUENCE(6,7,WORKDAY.INTL(D2+1,-1,"0111111"),1),IF(dt-DAY(dt)+1=D2,dt,""))
Dynamic array formulas.

 

[Dave Patton]

The following may also be relevant; it depends on your actual requirements.

Calendar Examples 2.xlsm
	A	B	C	D	E	F	G
1	Year	2023
2	Month	Mar		March 2023
3	Week	3
4	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
5	13-Mar-23	14-Mar-23	15-Mar-23	16-Mar-23	17-Mar-23	18-Mar-23	19-Mar-23
ISO Standard 8601 (3)

Cell Formulas
Range	Formula
D2	D2	=DATE(B1,MONTH(--(1&B2)),1)
A5:G5	A5	=LET(dt,SEQUENCE(1,7,WORKDAY.INTL(D2+LOOKUP(B3,{1,1;2,8;3,15;4,22;5,29;6,35}),-1,"0111111"),1),IF(dt-DAY(dt)+1=D2,dt,""))
Dynamic array formulas.