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 Rice.

What I really need is to extract and report the filtered Dates for each of the four examples. So for Eg3 I need a list of dates for every day in the period Nov2017, Dec2017, Jan2018 etc to Jun2018, then in the period between Nov2018, Dec2018, Jan2019 etc to Jun2019, and onward. After that, an average value for is obviously easy to compute.

New Eg5 should simplify:

MrExcel posts18.xlsx
	V	W
2	Example5
3	Start	End
4	12	1
5	29	2
6	2019	2020
7
8
9	Date	Average Value
10	extracted dates	-1.5
11	12/29/18	5
12	12/30/18	-10
13	12/31/18	-8
14	1/01/19	-10
15	1/02/19	7
16	12/29/19	8
17	12/30/19	-3
18	12/31/19	4
19	1/01/20	0
20	1/02/20	-8
Sheet11

Cell Formulas
Range	Formula
W10	W10	=AVERAGE(W11:W20)

 

[KRice]

I guess I still do not understand what you want. Let's just isolate on example three for now:

mrexcel_20200408.xlsx
	R	S
1
2	Example3
3	Start	End
4	11	1
5	6	16
6	2017	2020
7	11/6/2017	1/16/2020
8
9	Start Date	End Date
10	11/6/2017	1/16/2018
11	11/6/2018	1/16/2019
12	11/6/2019	1/16/2020
Sheet10

Cell Formulas
Range	Formula
R7:S7	R7	=DATE(R6,R4,R5)
R10	R10	=R$7
S10	S10	=DATE(IF(IFERROR(DATEDIF(R$7,DATE(R$6,S$4,S$5),"d"),9E+99)=9E+99,R$6+1,R$6),S$4,S$5)
R11:R12	R11	=IF(OR(S10=S$7,S10=""),"",DATE(YEAR(R10)+1,MONTH(R10),DAY(R10)))
S11:S12	S11	=IF(R11="","",DATE(YEAR(S10)+1,MONTH(S10),DAY(S10)))

Is it true that. in this specific case, you want three sets of information described by the start and end dates shown in the yellow cells above? I am confused by your last couple of posts where you mention Nov2017, Dec2017, etc. The start date shown is 6 Nov 2017. Do you want to ignore the day in November and include all of November 2017, or does "Nov2017" mean 6 Nov 2017 through 30 Nov 2017? The answer to these questions affects my next question, but I would like to clarify what information you would like to extract.

For any one set of information, do you want a direct listing of the date and its associated value for every day between x and y...where x and y are either the start and end dates I show in yellow cells (meaning 71 data points between 6 November and 16 January), or 91 data points between 1 November and 31 January.

And then you want to extract the same month/day range for every year described by your row 6 entries in the example blocks?

 

[DRSteele]

Did you see my Example5, which I included for simplicity? (And V6 should be 2018. I'm confusing everyone - sorry.)

I want all the dates between the starts and ends for the years specified. As in Eg5, Eg3 means all the dates (official Excel Date Serial numbers) Nov 6 2017 through Jan 16 2018, Nov 6 2018 through Jan 16 2019, and Nov 6 2019 through Jan 16 2020, all in a column with their Values beside them.

I want all the dates between the starts and ends for the years specified. But maybe we can forget the years for now - let's just figure out how to do the dates for all the years in the Dataset.

 

[KRice]

Sorry, I missed the details in Example 5. I see now that you do want a daily extraction for the month/day range. So the start and end dates in the yellow cells in my post #12 can be used to extract the data. I left the last version of my worksheet intact, but I expanded the number of columns to the right of example 3 to hold the extracted data sets. I also added some helper cells above each list of dates for each data set to define the start and end dates for that particular data set. For now, those helper cells are manually entered...I haven't figured out a clever way to automatically expand the table to accommodate the number of data sets that might be required, and to insert the start and end dates into every other column. The formulas to generate those date endpoints work okay, but placing them (in this case, in U5:U6, W5:W6, and Y5:Y6) and potentially expanding the number of columns needs to the resolved. I've left the data table out of my example, but you should be able to wire the formulas into yours, adjust any ranges, and generate something similar. Let me know if this is closer to the mark.

mrexcel_20200408.xlsx
	R	S	T	U	V	W	X	Y	Z
1
2	Example3
3	Start	End
4	11	1
5	6	16		11/6/2017	<-Start	11/6/2018	<-Start	11/6/2019	<-Start
6	2017	2020		1/16/2018	<-End	1/16/2019	<-End	1/16/2020	<-End
7	11/6/2017	1/16/2020
8				Set 1 - avg ->	-0.167	Set 2 - avg ->	-0.806	Set 3 - avg ->	-1.000
9	Start Date	End Date	Avg Value	Date	Value	Date	Value	Date	Value
10	11/6/2017	1/16/2018	-0.167	11/6/2017	-7	11/6/2018	1	11/6/2019	-6
11	11/6/2018	1/16/2019	-0.806	11/7/2017	-6	11/7/2018	-1	11/7/2019	8
12	11/6/2019	1/16/2020	-1.000	11/8/2017	-10	11/8/2018	4	11/8/2019	1
13				11/9/2017	7	11/9/2018	9	11/9/2019	10
14				11/10/2017	-10	11/10/2018	-1	11/10/2019	-3
15				11/11/2017	3	11/11/2018	-9	11/11/2019	-4
16				11/12/2017	7	11/12/2018	-6	11/12/2019	-7
17				11/13/2017	0	11/13/2018	0	11/13/2019	-8
18				11/14/2017	10	11/14/2018	-3	11/14/2019	-2
19				11/15/2017	-8	11/15/2018	-9	11/15/2019	-10
20				11/16/2017	1	11/16/2018	10	11/16/2019	9
21				11/17/2017	2	11/17/2018	7	11/17/2019	8
22				11/18/2017	7	11/18/2018	-3	11/18/2019	5
23				11/19/2017	-1	11/19/2018	9	11/19/2019	7
24				11/20/2017	-4	11/20/2018	5	11/20/2019	-7
25				11/21/2017	2	11/21/2018	0	11/21/2019	5
26				11/22/2017	6	11/22/2018	9	11/22/2019	-5
27				11/23/2017	-4	11/23/2018	-7	11/23/2019	0
28				11/24/2017	4	11/24/2018	8	11/24/2019	4
29				11/25/2017	-7	11/25/2018	-9	11/25/2019	-4
30				11/26/2017	0	11/26/2018	3	11/26/2019	2
Sheet10

Cell Formulas
Range	Formula
U5	U5	=R10
U6	U6	=S10
W5	W5	=R11
W6	W6	=S11
Y5	Y5	=R12
Y6	Y6	=S12
R7:S7	R7	=DATE(R6,R4,R5)
V8,Z8,X8	V8	=SUBTOTAL(1,V10:V82)
R10	R10	=R$7
S10	S10	=DATE(IF(IFERROR(DATEDIF(R$7,DATE(R$6,S$4,S$5),"d"),9E+99)=9E+99,R$6+1,R$6),S$4,S$5)
T10:T19	T10	=IFERROR(AVERAGEIFS($F$6:$F$3659,$B$6:$B$3659,">="&R10,$B$6:$B$3659,"<="&S10),"")
R11:R19	R11	=IF(OR(S10=S$7,S10=""),"",DATE(YEAR(R10)+1,MONTH(R10),DAY(R10)))
S11:S19	S11	=IF(R11="","",DATE(YEAR(S10)+1,MONTH(S10),DAY(S10)))
U10,Y10,W10	U10	=U5
V10:V30,Z10:Z30,X10:X30	V10	=IFERROR(INDEX($F$6:$F$3659,MATCH(U10,$B$6:$B$3659,0)),"")
U11:U30,Y11:Y30,W11:W30	U11	=IF(U10<U$6,U$10+ROWS(U$10:U10),"")

 

[StephenCrump]

	A	B	C	D	E	F	G	H
1
2	Date1	31 Oct 19
3	Date2	15 Jan 23
4
5		DateList	Values		Averages
6		1 Dec 19	6
7		26 Dec 19	45		31 Oct 19	31 Oct 20	31 Oct 21	31 Oct 22
8		15 Jan 20	6		15 Jan 20	15 Jan 21	15 Jan 22	15 Jan 23
9		29 Feb 20	12
10		3 Mar 20	33		19.00	23.00	32.33	22.67
11		1 Dec 20	17
12		26 Dec 20	19
13		15 Jan 21	33
14		28 Feb 21	49
15		3 Mar 21	16
16		1 Dec 21	23
17		26 Dec 21	29
18		15 Jan 22	45
19		28 Feb 22	49
20		3 Mar 22	30
21		15 May 22	6
22		18 Aug 22	18
23		1 Dec 22	7
24		26 Dec 22	12
25		15 Jan 23	49
26		28 Feb 23	1
27		3 Mar 23	6
Sheet1

Cell Formulas
Range	Formula
E7:H7	E7	=EDATE(Date1,SEQUENCE(,DATEDIF(Date1,Date2,"y")+1,0,12))
E8:H8	E8	=EDATE(DATE(YEAR(Date1)+(DATE(YEAR(Date1),MONTH(Date2),DAY(Date2))<Date1),MONTH(Date2),DAY(Date2)),SEQUENCE(,DATEDIF(Date1,Date2,"y")+1,0,12))
E10:H10	E10	=IFERROR(MMULT(SEQUENCE(,ROWS(DateList),,0),(DateList>=E7#)*(DateList<=E8#)*Values)/MMULT(SEQUENCE(,ROWS(DateList),,0),(DateList>=E7#)*(DateList<=E8#)),"-")
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Date1	=Sheet1!$B$2	E7:E8
Date2	=Sheet1!$B$3	E7:E8
DateList	=Sheet1!$B$6:$B$27	E10
Values	=Sheet1!$C$6:$C$27	E10

 

[KRice]

Here is one more attempt. I left the time block summary intact, and used the start/end dates in that list to construct long sequential lists of dates, similar to what you show in Example 5 of your post #11 (see my Example 2 below for comparison). I needed to add a helper column called "index" to help construct the sequential date lists. Finally, I pulled the values from the main data table and computed an overall average, shown in row 23. The averages of the individual date lists were shown previously in the summary blocks found in rows 10-22.

mrexcel_20200408.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1
2										Example1				Example2				Example3				Example4
3										Start	End			Start	End			Start	End			Start	End
4									Month	4	11			12	1			11	1			10	2
5		Date	Month	Day	Year	Value			Day	11	14			29	2			6	16			23	9
6		4/11/2010	4	11	2010	8			Year	2010	2013			2018	2020			2017	2020			2019	2020
7		4/12/2010	4	12	2010	7			Date	4/11/2010	11/14/2013			12/29/2018	1/2/2020			11/6/2017	1/16/2020			10/23/2019	2/9/2020
8		4/13/2010	4	13	2010	-6						0.016				3.100				0.139				0.527
9		4/14/2010	4	14	2010	1				Start Date	End Date	Avg Value		Start Date	End Date	Avg Value		Start Date	End Date	Avg Value		Start Date	End Date	Avg Value
10		4/15/2010	4	15	2010	8				4/11/2010	11/14/2010	0.624		12/29/2018	1/2/2019	4.800		11/6/2017	1/16/2018	-0.486		10/23/2019	2/9/2020	0.527
11		4/16/2010				-3				4/11/2011	11/14/2011	-0.404		12/29/2019	1/2/2020	1.400		11/6/2018	1/16/2019	0.458
12		4/17/2010				-6				4/11/2012	11/14/2012	-0.454						11/6/2019	1/16/2020	0.444
13		4/18/2010				-9				4/11/2013	11/14/2013	0.298
14		4/19/2010				8
15		4/20/2010				8
16		4/21/2010				-3
17		4/22/2010				-10
18		4/23/2010				3
19		4/24/2010				-5
20		4/25/2010				10
21		4/26/2010				0
22		4/27/2010				8
23		4/28/2010				10					0.016	<-Avg			3.100	<-Avg			0.139	<-Avg			0.527	<-Avg
24		4/29/2010				0				Date	Value	Index		Date	Value	Index		Date	Value	Index		Date	Value	Index
25		4/30/2010				-10				4/11/2010	8	1		12/29/2018	7	1		11/6/2017	-6	1		10/23/2019	3	1
26		5/1/2010				-3				4/12/2010	7	1		12/30/2018	9	1		11/7/2017	7	1		10/24/2019	-4	1
27		5/2/2010				6				4/13/2010	-6	1		12/31/2018	2	1		11/8/2017	-8	1		10/25/2019	10	1
28		5/3/2010				2				4/14/2010	1	1		1/1/2019	-4	1		11/9/2017	-9	1		10/26/2019	5	1
29		5/4/2010				10				4/15/2010	8	1		1/2/2019	10	1		11/10/2017	-9	1		10/27/2019	-10	1
30		5/5/2010				4				4/16/2010	-3	1		12/29/2019	-4	2		11/11/2017	-10	1		10/28/2019	6	1
31		5/6/2010				-1				4/17/2010	-6	1		12/30/2019	6	2		11/12/2017	-5	1		10/29/2019	2	1
32		5/7/2010				4				4/18/2010	-9	1		12/31/2019	0	2		11/13/2017	-9	1		10/30/2019	9	1
33		5/8/2010				1				4/19/2010	8	1		1/1/2020	4	2		11/14/2017	10	1		10/31/2019	4	1
34		5/9/2010				4				4/20/2010	8	1		1/2/2020	1	2		11/15/2017	1	1		11/1/2019	-4	1
35		5/10/2010				-1				4/21/2010	-3	1				3		11/16/2017	5	1		11/2/2019	-7	1
36		5/11/2010				7				4/22/2010	-10	1				4		11/17/2017	10	1		11/3/2019	-10	1
37		5/12/2010				2				4/23/2010	3	1				5		11/18/2017	8	1		11/4/2019	-6	1
38		5/13/2010				0				4/24/2010	-5	1						11/19/2017	-3	1		11/5/2019	3	1
39		5/14/2010				-9				4/25/2010	10	1						11/20/2017	9	1		11/6/2019	7	1
40		5/15/2010				10				4/26/2010	0	1						11/21/2017	-4	1		11/7/2019	-4	1
41		5/16/2010				2				4/27/2010	8	1						11/22/2017	3	1		11/8/2019	2	1
Sheet10

Cell Formulas
Range	Formula
C6:C10	C6	=MONTH(B6)
D6:D10	D6	=DAY(B6)
E6:E10	E6	=YEAR(B6)
V7:W7,R7:S7,N7:O7,J7:K7	J7	=DATE(J6,J4,J5)
L8,X8,T8,P8	L8	=AVERAGE(L10:L22)
J10,V10,R10,N10	J10	=J$7
V11:V22,R11:R22,N11:N22,J11:J22	J11	=IF(OR(K10=K$7,K10=""),"",DATE(YEAR(J10)+1,MONTH(J10),DAY(J10)))
X10:X22,T10:T22,P10:P22,L10:L22	L10	=IFERROR(AVERAGEIFS($F$6:$F$3659,$B$6:$B$3659,">="&J10,$B$6:$B$3659,"<="&K10),"")
K10,W10,S10,O10	K10	=DATE(IF(IFERROR(DATEDIF(J$7,DATE(J$6,K$4,K$5),"d"),9E+99)=9E+99,J$6+1,J$6),K$4,K$5)
W11:W22,S11:S22,O11:O22,K11:K22	K11	=IF(J11="","",DATE(YEAR(K10)+1,MONTH(K10),DAY(K10)))
K23,W23,S23,O23	K23	=SUBTOTAL(1,K25:K4000)
J25,V25,R25,N25	J25	=J10
K25:K41,W25:W41,S25:S41,O25:O37	K25	=IFERROR(INDEX($F$6:$F$3659,MATCH(J25,$B$6:$B$3659,0)),"")
V26:V41,R26:R41,N26:N37,J26:J41	J26	=IF(L25=0,"",IF(L25=L26,J25+1,OFFSET(J$9,L26,0)))
X26:X41,T26:T41,P26:P37,L26:L41	L26	=IF(J25=OFFSET(J$9,L25,1),IF(OFFSET(J$9,L25+1,0)=0,0,L25+1),L25)
F6:F41	F6	=RANDBETWEEN(-10,10)

 

[DRSteele]

Rice, thank you immensely for all your efforts. I'm just trying to test all this and make sure it works right. I haven't forgotten about you......

 

[KRice]

That's fine...please let me know how that goes. I ran a few checks on the setup by comparing the overall average (K23 in my post #16) to the average of the average values for each time period that repeats year after year in the time block summary (e.g., L8 in post #16)...normally taking the average of averages is ill-advised, but in this case, it's okay since each component is equally weighted (the number of days represented by each time period is the same, unless the time span of interest includes the end of February in a leap year, which would introduce a discrepancy). The L8 formula can be eliminated in the final setup, as could columns C, D, and E, as they are not used elsewhere (unless you are using them for something else).

I recommend focusing on setting up just one example block to get the ranges adjusted for your setup. And you might even want to do that in two steps: 1) set up just the time block summary first (e.g., J9:J22 in post #16), and confirm that the time period end points are correct, that the range references are correct, and that an average value is reported back for each period; and then in step 2) add in the lengthy sequential listing of dates and values. When doing this second step, note that the top two rows require special attention (J25:L26 in example 1 of post #16) in order to tie them into the time block summary list above. J25 references the first starting date in the time block summary, K25 pulls the value corresponding to the date in J25, and for the helper cell L25, you enter a "1" to establish that the starting date belongs to index 1 of the dates shown above in the time summary block. K26 is trivial, as it is pulled down from K25. Then L26 looks at the previous date (e.g., J25) to determine if the end of its corresponding time block has been reached...if so, then the index is increased by 1 to move to the next time block, and if not, the index remains unchanged so that the next date in the sequence can be generated. Then J26 either increments the date by 1 day, or it jumps to the stating date of the next time block, depending on the value in the L26 helper cell. Once these two rows are set up, select K26:L26 and pull down until no more valid dates are generated.

 

[DRSteele]

I think I got it to work. This extracts all the dates and other data from the periods desired and spills them down in one column. Here, I wanted to extract all the data over Christmas for the years 2017 to 2020.

The raw data is in an Excel Table called T_Data, which resides in B5. [It's the same data as before without the SEQUENCE and RANDBETWEEN formulas, which I put there so everyone could re-create the data easily.]

There is another Table called T_Parameters which resides in I3; it contains the conditions.

The dynamic array formula has an IF statement because the appropriate FILTER formula depends upon whether the Start month is a larger number than the End month. Notice there is a new stat I created to ease formula construction; it's a conglomeration of the month + day/100.

Change the values in J4 to L5 and everything should function right. There will be an error if the Year set is illogical.

I would be very grateful if I could try all your patience a little more to test this out. Thanks ever so much for everything already.

MrExcel posts18.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N
3								Period	Month	Day	Year	Date	MD
4								Start	12	25	2017	12/25/2017	12.25
5	Date	Month	Day	Year	Value	MD		End	1	2	2020	1/2/2020	1.02
6	4/11/10	4	11	2010	-3	4.11
7	4/12/10	4	12	2010	-10	4.12		Date	Month	Day	Year	Value	MD
8	4/13/10	4	13	2010	0	4.13		12/25/2017	12	25	2017	4	12.25
9	4/14/10	4	14	2010	0	4.14		12/26/2017	12	26	2017	4	12.26
10	4/15/10	4	15	2010	4	4.15		12/27/2017	12	27	2017	-5	12.27
11	4/16/10	4	16	2010	-2	4.16		12/28/2017	12	28	2017	9	12.28
12	4/17/10	4	17	2010	-6	4.17		12/29/2017	12	29	2017	4	12.29
13	4/18/10	4	18	2010	3	4.18		12/30/2017	12	30	2017	4	12.3
14	4/19/10	4	19	2010	9	4.19		12/31/2017	12	31	2017	-6	12.31
15	4/20/10	4	20	2010	-9	4.20		1/1/2018	1	1	2018	-9	1.01
16	4/21/10	4	21	2010	10	4.21		1/2/2018	1	2	2018	-2	1.02
17	4/22/10	4	22	2010	-6	4.22		12/25/2018	12	25	2018	-6	12.25
18	4/23/10	4	23	2010	9	4.23		12/26/2018	12	26	2018	8	12.26
19	4/24/10	4	24	2010	-9	4.24		12/27/2018	12	27	2018	6	12.27
20	4/25/10	4	25	2010	2	4.25		12/28/2018	12	28	2018	4	12.28
21	4/26/10	4	26	2010	-9	4.26		12/29/2018	12	29	2018	-2	12.29
22	4/27/10	4	27	2010	-8	4.27		12/30/2018	12	30	2018	-4	12.3
23	4/28/10	4	28	2010	-7	4.28		12/31/2018	12	31	2018	4	12.31
24	4/29/10	4	29	2010	6	4.29		1/1/2019	1	1	2019	2	1.01
25	4/30/10	4	30	2010	-6	4.30		1/2/2019	1	2	2019	-4	1.02
26	5/1/10	5	1	2010	6	5.01		12/25/2019	12	25	2019	8	12.25
27	5/2/10	5	2	2010	-6	5.02		12/26/2019	12	26	2019	-1	12.26
28	5/3/10	5	3	2010	2	5.03		12/27/2019	12	27	2019	6	12.27
29	5/4/10	5	4	2010	3	5.04		12/28/2019	12	28	2019	10	12.28
30	5/5/10	5	5	2010	9	5.05		12/29/2019	12	29	2019	4	12.29
31	5/6/10	5	6	2010	-9	5.06		12/30/2019	12	30	2019	10	12.3
32	5/7/10	5	7	2010	1	5.07		12/31/2019	12	31	2019	10	12.31
33	5/8/10	5	8	2010	5	5.08		1/1/2020	1	1	2020	-8	1.01
34	5/9/10	5	9	2010	-5	5.09		1/2/2020	1	2	2020	-10	1.02
35	5/10/10	5	10	2010	-3	5.10
Sheet11 (2)

Cell Formulas
Range	Formula
M4:M5	M4	=DATE(L4,J4,K4)
N4:N5,G6:G35	N4	=[@Month]+[@Day]/100
I8:N34	I8	=IF(J4>J5,FILTER(T_Data,((T_Data[MD]>=N4)+(T_Data[MD]<=N5)*(T_Data[Year]>=L4))*(T_Data[Year]<=L5)*(T_Data[Date]>=M4)*(T_Data[Date]<=M5)),FILTER(T_Data,(T_Data[MD]>=N4)*(T_Data[MD]<=N5)*(T_Data[Year]>=L4)*(T_Data[Year]<=L5)*(T_Data[Date]>=M4)*(T_Data[Date]<=M5)))
Dynamic array formulas.

 

[StephenCrump]

Hi Don, I'm not sure whether you saw Post #15?

I've since simplified the formula for the period end dates, and these now adjust correctly for leap years. Here are two different ways to get the desired results:

	A	B	C	D	E	F	G	H	I	J	K
1
2	Date1	23 Dec 20
3	Date2	15 Jan 23
4
5		DateList	Values		Averages
6		1 Dec 19	81
7		26 Dec 19	46		23 Dec 20	23 Dec 21	23 Dec 22
8		29 Feb 20	45		15 Jan 21	15 Jan 22	15 Jan 23
9		1 Dec 20	87
10		26 Dec 20	70		45.00	63.50	51.80		45.00	63.50	51.80
11		12 Jan 21	55
12		15 Jan 21	10		Dates				Dates
13		28 Feb 21	28		26 Dec 20				26 Dec 20	26 Dec 21	26 Dec 22
14		3 Mar 21	53		12 Jan 21				12 Jan 21	15 Jan 22	27 Dec 22
15		1 Dec 21	10		15 Jan 21				15 Jan 21		28 Dec 22
16		26 Dec 21	86		26 Dec 21						29 Dec 22
17		15 Jan 22	41		15 Jan 22						15 Jan 23
18		28 Feb 22	82		26 Dec 22
19		3 Mar 22	96		27 Dec 22
20		4 Mar 22	59		28 Dec 22
21		10 Mar 22	24		29 Dec 22
22		11 Mar 22	5		15 Jan 23
23		14 Mar 22	48
24		15 May 22	56
25		18 Aug 22	25
26		1 Dec 22	69
27		26 Dec 22	57
28		27 Dec 22	12
29		28 Dec 22	17
30		29 Dec 22	76
31		15 Jan 23	97
32		28 Feb 23	1
33		3 Mar 23	84
Sheet1

Cell Formulas
Range	Formula
E7:G7	E7	=EDATE(Date1,SEQUENCE(,DATEDIF(Date1,Date2,"y")+1,0,12))
E8:G8	E8	=EDATE(Date2,SEQUENCE(,DATEDIF(Date1,Date2,"y")+1,-12*DATEDIF(Date1,Date2,"y"),12))
E10:G10	E10	=IFERROR(MMULT(SEQUENCE(,ROWS(DateList),,0),(DateList>=E7#)*(DateList<=E8#)*Values)/MMULT(SEQUENCE(,ROWS(DateList),,0),(DateList>=E7#)*(DateList<=E8#)),"-")
I10:K10	I10	=AVERAGE(FILTER(Values,(DateList>=E7)*(DateList<=E8)))
E13:E22	E13	=FILTER(DateList,MMULT((DateList>=E7#)*(DateList<=E8#),SEQUENCE(DATEDIF(Date1,Date2,"y")+1,,,0)))
I13:I15,K13:K17,J13:J14	I13	=FILTER(DateList,(DateList>=E7)*(DateList<=E8))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Date1	=Sheet1!$B$2	E13, E7:E8
Date2	=Sheet1!$B$3	E13, E7:E8
DateList	=Sheet1!$B$6:$B$33	I13:K13, I10:K10, E13, E10
Values	=Sheet1!$C$6:$C$33	I10:K10, E10