Return dates in month based on cell value

[Nat321]

Hello everyone!

I'm trying to include an "interactive" button/slicer option to list out all dates in the current month, based on the day in column D.
If I click on D2, K2 to K5 will list out all all Tuesdays based on K1.
K1 =today()

TIA!

 

[Nat321]

Hello everyone!

I'm trying to include an "interactive" button/slicer option to list out all dates in the current month, based on the day in column D.
If I click on D2, K2 to K5 will list out all all Tuesdays based on K1.
K1 =today()

TIA!

View attachment 71011

Adding on as a reply as I can't find the edit option.

Is there a simpler way than listing out all dates in the year and use it as a database for the slicer?

 

[Dave Patton]

Consider the following
1 You can use Tuesday or Tue
2 You can use name manager to name DOWS ={"Mon","Tue","Wed","Thu","Fri","Sat","Sun"} but that is not essential.
3 "Is there a simpler way than listing out all dates in the year and use it as a database for the slicer?" What dates do you want listed?

T202208a.xlsm
	A	B									K
1		Day									August 7, 2022
2	1	Tuesday									Tue, Aug 02, 2022
3	2										Tue, Aug 09, 2022
4	3										Tue, Aug 16, 2022
5	4										Tue, Aug 23, 2022
6	5										Tue, Aug 30, 2022
7	6
8	7
2e

Cell Formulas
Range	Formula
K2	K2	=WORKDAY.INTL($K$1-DAY($K$1)+1,A2,REPLACE("1111111",MATCH(LEFT($B$2,3),{"Mon","Tue","Wed","Thu","Fri","Sat","Sun"},0),1,0))
K3:K6	K3	=WORKDAY.INTL($K$1-DAY($K$1)+1,A3,REPLACE("1111111",MATCH(LEFT($B$2,3),DOWS,0),1,0))

 

[Nat321]

Consider the following
1 You can use Tuesday or Tue
2 You can use name manager to name DOWS ={"Mon","Tue","Wed","Thu","Fri","Sat","Sun"} but that is not essential.
3 "Is there a simpler way than listing out all dates in the year and use it as a database for the slicer?" What dates do you want listed?

T202208a.xlsm
	A	B									K
1		Day									August 7, 2022
2	1	Tuesday									Tue, Aug 02, 2022
3	2										Tue, Aug 09, 2022
4	3										Tue, Aug 16, 2022
5	4										Tue, Aug 23, 2022
6	5										Tue, Aug 30, 2022
7	6
8	7
2e

Cell Formulas
Range	Formula
K2	K2	=WORKDAY.INTL($K$1-DAY($K$1)+1,A2,REPLACE("1111111",MATCH(LEFT($B$2,3),{"Mon","Tue","Wed","Thu","Fri","Sat","Sun"},0),1,0))
K3:K6	K3	=WORKDAY.INTL($K$1-DAY($K$1)+1,A3,REPLACE("1111111",MATCH(LEFT($B$2,3),DOWS,0),1,0))

Thanks! It works, but i'm not sure of the need to replace 1111111?

3) I listed out all the dates in the year and just copied the result out...

 

[Dave Patton]

"Thanks! It works, but i'm not sure of the need to replace 1111111?"

You want to specify a variable day of the week. This part of the formula replaces the 1 with a 0 for the selected Day of the week.
Review the formula with Excels Formula Evaluate Formula and review Excel's help for Workday.intl.

 

[Nat321]

"Thanks! It works, but i'm not sure of the need to replace 1111111?"

You want to specify a variable day of the week. This part of the formula replaces the 1 with a 0 for the selected Day of the week.
Review the formula with Excels Formula Evaluate Formula and review Excel's help for Workday.intl.

Understood. Thanks Dave for your help!

 

[Dave Patton]

Thanks for the feedback.
A slightly different approach and also using Let to show blank where necessary.

T202208a.xlsm
	A	B
20	Require	Fri
21	For date	Sun Aug 07, 22
22		Fri Aug 05, 22
23		Fri Aug 12, 22
24		Fri Aug 19, 22
25		Fri Aug 26, 22
26
2e

Cell Formulas
Range	Formula
B22:B26	B22	=LET(x,WORKDAY.INTL($B$21-DAY($B$21)+1,ROW()-21,REPLACE("1111111",MATCH(LEFT($B$20,3),DOWS,0),1,0)),IF(x>EOMONTH($B$21,0)," ",x))