VBA Date Function

[jhmathis]

Hello,

I'm wondering if it's possible to create macro with a date function that would get a range of rows as specified by a date range. For example, I have an Excel file that has a column of dates in the format of x/xx/xxxx; now let's suppose today's date is 2/25/2023 and I'd like to get all the data (rows) between last week Friday (2/17/2023) and this past Thursday (2/23/2023). Basically I'm looking for a function that will parse the date column and always find last week Thursday data (rows) to this week Friday for any given week. Appreciate any pointers and/or assitance with this

Thanks

 

[James006]

Hello,
Previous Thursday : =TODAY()-MOD(WEEKDAY(TODAY())+2,7)
Next Friday : =TODAY()+7-WEEKDAY(TODAY()+1)

 

[awoohaw]

Do you strictly need vba code to do this? Is FILTER available in Excel 2021?
Here is a solution with Let and Filter, put any date in F1 and it will give you the week from Thurs thru Wed that that date is in.

Book1
	A	B	C	D	E	F	G	H	I
1					StartDay:	Saturday, February 11, 2023
2
3	Date	Data1	Data2	Data3
4	2023-01-26	RXB	500	500		Date	Data1	Data2	Data3
5	2023-01-27	YHO	300	400		2023-02-09	YUG	300	300
6	2023-01-28	KHF	300	300		2023-02-10	QYM	400	300
7	2023-01-29	KLP	300	400		2023-02-11	AYE	400	300
8	2023-01-30	MQD	300	400		2023-02-12	PAO	500	400
9	2023-01-31	SDF	300	400		2023-02-13	MDE	400	300
10	2023-02-01	BYV	300	400		2023-02-14	CVZ	500	400
11	2023-02-02	DDT	400	400		2023-02-15	SFL	300	400
12	2023-02-03	MHC	300	500
13	2023-02-04	VII	400	300
14	2023-02-05	WPU	500	500
15	2023-02-06	DTR	300	500
16	2023-02-07	BLG	500	400
17	2023-02-08	KTW	500	500
18	2023-02-09	YUG	300	300
19	2023-02-10	QYM	400	300
20	2023-02-11	AYE	400	300
21	2023-02-12	PAO	500	400
22	2023-02-13	MDE	400	300
23	2023-02-14	CVZ	500	400
24	2023-02-15	SFL	300	400
25	2023-02-16	TLO	400	300
26	2023-02-17	KHZ	500	500
27	2023-02-18	UZJ	400	400
28	2023-02-19	FAK	400	300
29	2023-02-20	FTD	300	400
30	2023-02-21	MXR	500	300
31	2023-02-22	GGS	300	400
32	2023-02-23	REZ	500	400
33	2023-02-24	XLG	400	300
34	2023-02-25	YEM	300	500
35	2023-02-26	KFT	400	300
36	2023-02-27	GPD	400	300
37	2023-02-28	TMD	300	500
38	2023-03-01	GRO	500	400
Sheet1

Cell Formulas
Range	Formula
F4:I11	F4	=LET( Top,$A$3:$D$3, Data,$A:$D, Dates,$A:$A, SearchDate,$F$1, Start,WORKDAY.INTL(SearchDate +1,-1,"1110111"), End,WORKDAY.INTL(SearchDate,1,"1110111"), Bottom,FILTER(Data,(Dates>=Start)*(Dates<End),""), VSTACK(Top,Bottom))
Dynamic array formulas.

 

[jhmathis]

Hello,
Previous Thursday : =TODAY()-MOD(WEEKDAY(TODAY())+2,7)
Next Friday : =TODAY()+7-WEEKDAY(TODAY()+1)

Thanks

 

[jhmathis]

Do you strictly need vba code to do this? Is FILTER available in Excel 2021?
Here is a solution with Let and Filter, put any date in F1 and it will give you the week from Thurs thru Wed that that date is in.

Book1
	A	B	C	D	E	F	G	H	I
1					StartDay:	Saturday, February 11, 2023
2
3	Date	Data1	Data2	Data3
4	2023-01-26	RXB	500	500		Date	Data1	Data2	Data3
5	2023-01-27	YHO	300	400		2023-02-09	YUG	300	300
6	2023-01-28	KHF	300	300		2023-02-10	QYM	400	300
7	2023-01-29	KLP	300	400		2023-02-11	AYE	400	300
8	2023-01-30	MQD	300	400		2023-02-12	PAO	500	400
9	2023-01-31	SDF	300	400		2023-02-13	MDE	400	300
10	2023-02-01	BYV	300	400		2023-02-14	CVZ	500	400
11	2023-02-02	DDT	400	400		2023-02-15	SFL	300	400
12	2023-02-03	MHC	300	500
13	2023-02-04	VII	400	300
14	2023-02-05	WPU	500	500
15	2023-02-06	DTR	300	500
16	2023-02-07	BLG	500	400
17	2023-02-08	KTW	500	500
18	2023-02-09	YUG	300	300
19	2023-02-10	QYM	400	300
20	2023-02-11	AYE	400	300
21	2023-02-12	PAO	500	400
22	2023-02-13	MDE	400	300
23	2023-02-14	CVZ	500	400
24	2023-02-15	SFL	300	400
25	2023-02-16	TLO	400	300
26	2023-02-17	KHZ	500	500
27	2023-02-18	UZJ	400	400
28	2023-02-19	FAK	400	300
29	2023-02-20	FTD	300	400
30	2023-02-21	MXR	500	300
31	2023-02-22	GGS	300	400
32	2023-02-23	REZ	500	400
33	2023-02-24	XLG	400	300
34	2023-02-25	YEM	300	500
35	2023-02-26	KFT	400	300
36	2023-02-27	GPD	400	300
37	2023-02-28	TMD	300	500
38	2023-03-01	GRO	500	400
Sheet1

Cell Formulas
Range	Formula
F4:I11	F4	=LET( Top,$A$3:$D$3, Data,$A:$D, Dates,$A:$A, SearchDate,$F$1, Start,WORKDAY.INTL(SearchDate +1,-1,"1110111"), End,WORKDAY.INTL(SearchDate,1,"1110111"), Bottom,FILTER(Data,(Dates>=Start)*(Dates<End),""), VSTACK(Top,Bottom))
Dynamic array formulas.

Thanks

 

[awoohaw]

My Pleasure. Happy to help. Best wishes.