Dynamic Formula to extract data before and after dates

[DRSteele]

Can someone please help me?

I am looking to create a formula using function FILTER to extract data using dates as criteria. As you can see, it is not as easy as expected. The problems arise when the Start Month is a higher value than the End Month because it wraps around the year and when the Start Day is a higher value than the End Day because it wraps around a month.

I want to use function FILTER to extract and report the filtered Dates and the average of Value for each of the four examples.

[The formula in F10 obviously goes all the way down. I did not paste all the data here.]

MrExcel posts18.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1
2									Example1			Example2			Example3			Example4
3									Start	End		Start	End		Start	End		Start	End
4								Month	2	3		4	5		11	1		10	2
5	Date	Month	Day	Year	Value			Day	1	28		20	7		6	16		23	9
6	4/11/2010	4	11	2010	-10			Year	2019	2020		2005	2009		2017	2020		2019	2020
7	4/12/2010	4	12	2010	2			Date	2/1/2019	3/28/2020		4/20/2005	5/7/2009		11/6/2017	1/16/2020		10/23/2019	2/9/2020
8	4/13/2010	4	13	2010	-4
9	4/14/2010	4	14	2010	-9				Date	Average Value		Date	Average Value		Date	Average Value		Date	Average Value
10	4/15/2010	4	15	2010	0				need dynamic formulas			need dynamic formulas			need dynamic formulas			need dynamic formulas
Sheet11

Cell Formulas
Range	Formula
B6:B3655	B6	=SEQUENCE(3650,,40279)
C6:C3655	C6	=MONTH(B6#)
D6:D3655	D6	=DAY(B6#)
E6:E3655	E6	=YEAR(B6#)
J7:K7,S7:T7,P7:Q7,M7:N7	J7	=DATE(J6,J4,J5)
F6:F10	F6	=RANDBETWEEN(-10,10)
Dynamic array formulas.

 

[DRSteele]

Thanks Stephen.

Your algorithm does not seem to extract the dates for the periods in question. See how my example in Post#19 extracts the relevant dates from the list; specifically, all the dates between Dec25 and Jan2 for the dates starting Dec 25, 2017 and ending Jan 2, 2020. It will also work for other dates; e.g., if you pick Apr1 to Apr14 for the dates starting Apr 1, 2016 and ending Apr 14, 2020.

 

[StephenCrump]

Your algorithm does not seem to extract the dates for the periods in question ...

Like this?

	A	B	C	D	E	F	G	H
1					StartDate	25 Dec 19
2					EndDate	2 Jan 23
3
4		Dates	Values		25 Dec 19	25 Dec 20	25 Dec 21	25 Dec 22
5		20 Dec 19	81		2 Jan 20	2 Jan 21	2 Jan 22	2 Jan 23
6		21 Dec 19	46
7		22 Dec 19	45		Averages
8		23 Dec 19	87		51.22	52.89	58.89	66.56
9		24 Dec 19	70	OR	51.22	52.89	58.89	66.56
10		25 Dec 19	55
11		26 Dec 19	10		Filter
12		27 Dec 19	28		25 Dec 19
13		28 Dec 19	53		26 Dec 19
14		29 Dec 19	10		27 Dec 19
15		30 Dec 19	86		28 Dec 19
16		31 Dec 19	41		29 Dec 19
17		1 Jan 20	82		30 Dec 19
18		2 Jan 20	96		31 Dec 19
19		3 Jan 20	59		1 Jan 20
20		4 Jan 20	24		2 Jan 20
21		20 Dec 20	5		25 Dec 20
22		21 Dec 20	48		26 Dec 20
23		22 Dec 20	56		27 Dec 20
24		23 Dec 20	25		28 Dec 20
25		24 Dec 20	69		29 Dec 20
26		25 Dec 20	57		30 Dec 20
27		26 Dec 20	12		31 Dec 20
28		27 Dec 20	17		1 Jan 21
29		28 Dec 20	76		2 Jan 21
30		29 Dec 20	97		25 Dec 21
31		30 Dec 20	1		26 Dec 21
32		31 Dec 20	84		27 Dec 21
33		1 Jan 21	91		28 Dec 21
34		2 Jan 21	41		29 Dec 21
35		3 Jan 21	3		30 Dec 21
36		4 Jan 21	82		31 Dec 21
37		20 Dec 21	69		1 Jan 22
38		21 Dec 21	77		2 Jan 22
39		22 Dec 21	10		25 Dec 22
40		23 Dec 21	88		26 Dec 22
41		24 Dec 21	62		27 Dec 22
42		25 Dec 21	79		28 Dec 22
43		26 Dec 21	32		29 Dec 22
44		27 Dec 21	4		30 Dec 22
45		28 Dec 21	87		31 Dec 22
46		29 Dec 21	87		1 Jan 23
47		30 Dec 21	64		2 Jan 23
48		31 Dec 21	60
49		1 Jan 22	93
50		2 Jan 22	24
51		3 Jan 22	60
52		4 Jan 22	5
53		20 Dec 22	87
54		21 Dec 22	33
55		22 Dec 22	39
56		23 Dec 22	96
57		24 Dec 22	20
58		25 Dec 22	59
59		26 Dec 22	95
60		27 Dec 22	88
61		28 Dec 22	62
62		29 Dec 22	32
63		30 Dec 22	73
64		31 Dec 22	89
65		1 Jan 23	46
66		2 Jan 23	55
67		3 Jan 23	90
68		4 Jan 23	38
69
Sheet1

Cell Formulas
Range	Formula
E4:H4	E4	=EDATE(StartDate,SEQUENCE(,DATEDIF(StartDate,EndDate,"y")+1,0,12))
E5:H5	E5	=EDATE(EndDate,SEQUENCE(,DATEDIF(StartDate,EndDate,"y")+1,-12*DATEDIF(StartDate,EndDate,"y"),12))
E8:H8	E8	=IFERROR(MMULT(SEQUENCE(,ROWS(MyTable),,0),(MyTable[Dates]>=E4#)*(MyTable[Dates]<=E5#)*MyTable[Values])/MMULT(SEQUENCE(,ROWS(MyTable),,0),(MyTable[Dates]>=E4#)*(MyTable[Dates]<=E5#)),"-")
E9:H9	E9	=AVERAGE(FILTER(MyTable[Values],(MyTable[Dates]>=E4)*(MyTable[Dates]<=E5)))
E12:E47	E12	=FILTER(MyTable[Dates],MMULT((MyTable[Dates]>=E4#)*(MyTable[Dates]<=E5#),SEQUENCE(DATEDIF(StartDate,EndDate,"y")+1,,,0)))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
EndDate	=Sheet1!$F$2	E12, E4:E5
StartDate	=Sheet1!$F$1	E12, E4:E5

 

[DRSteele]

Yes, that produces the result. Thanks tons, Stephen. Now we have two ways!

 

[KRice]

My post #16 offers a 3rd solution to obtain the desired sequential date segments in a single list, although the approach is clunky since it relies on some helper cells above to create start and end dates for each segment, and another helper column to provide an index for navigating those date segments. Your efficient solutions illustrate some advantages of the improved function set available in 365, which has me considering moving on to that version. Still, I wanted to mention this in case someone without 365 is attempting to do something similar.