Diff between two dates summarized by months

[spurs50]

I haven't been able to work out how to do in power pivot or normal formulas as it requires using the average of each month and dividing by the occurrences in each month to get an average.
The problem is the start and end dates are random, and days in between can be any number of days between different months and years.
Some rows may be between 1st Sep 2020, and 20th Dec 2020, or just be a week long as an example below. All dates are different and randomn

I would like to get a table of per month calculation for each country in a pivot table format or similar.
Example below raw data

Country Plant Start End Days Volume
Brazil Rio 10/15/2022 12/20/2022 67 50
Brazil Brasilia 02/06/2023 03/07/2023 30 100
Colombia Bogota 11/01/2022 12/05/2022 35 30

The answer should look like below for each plant. And then I can summarize by country. Think pivot tables are impossible and needs formulas or vba?
Any help would be greatly appreciated

	Brazil	Brazil	Colombia
	Rio	Brasilia	Bogota
Oct-22	27.42	0.00	0.00
Nov-22	50.00	0.00	30.00
Dec-22	32.26	0.00	4.84
Jan-23	0.00	0.00	0.00
Feb-23	0.00	82.14	0.00
Mar-23	0.00	22.58	0.00

 

[Z51]

Can you explain why, for example, Brazil>Rio is 27.42, 50.00 and 32.36? And why the others are they way they are? (e.g. What is the math you used to arrive at those values?) It's not making sense to me at this point.

 

[spurs50]

Can you explain why, for example, Brazil>Rio is 27.42, 50.00 and 32.36? And why the others are they way they are? (e.g. What is the math you used to arrive at those values?) It's not making sense to me at this point.

Sorry I should have been clearer. Rio has 50 power used in each day between Oct 15th and 20th Dec. Oct has 31 days, started on 15th Oct, so it used 17 days in Oct. (50*17)/31 = 27.42
It used power each day in Nov, so avg 50 (50*30)/30 days Nov). Dec finished on 20th Dec = 20 days. (50*20) /31 days in Dec = 32.26
Thanks

 

[Z51]

Perhaps this will work? I used helper cells in rows 8-10 so I could shorten the formulas in rows 11+.

Book1
	A	B	C	D	E	F
1	Country	Plant	Start	End	Days	Volume
2	Brazil	Rio	10/15/2022	12/20/2022	67	50
3	Brazil	Brasilia	2/6/2023	3/7/2023	30	100
4	Colombia	Bogota	11/1/2022	12/5/2022	35	30
5
6		Brazil	Brazil	Colombia
7		Rio	Brasilia	Bogota
8		10/15/2022	2/6/2023	11/1/2022
9		12/20/2022	3/7/2023	12/5/2022
10		50	100	30
11	Aug-22	0.00	0.00	0.00
12	Sep-22	0.00	0.00	0.00
13	Oct-22	27.42	0.00	0.00
14	Nov-22	50.00	0.00	30.00
15	Dec-22	32.26	0.00	4.84
16	Jan-23	0.00	0.00	0.00
17	Feb-23	0.00	82.14	0.00
18	Mar-23	0.00	22.58	0.00
19	Apr-23	0.00	0.00	0.00
20	May-23	0.00	0.00	0.00
21	Jun-23	0.00	0.00	0.00
22	Jul-23	0.00	0.00	0.00
23	Aug-23	0.00	0.00	0.00
24	Sep-23	0.00	0.00	0.00
25	Oct-23	0.00	0.00	0.00
26	Nov-23	0.00	0.00	0.00
27	Dec-23	0.00	0.00	0.00
Sheet3

Cell Formulas
Range	Formula
E2:E4	E2	=DATEDIF(C2,D2,"d")+1
B8:D8	B8	=INDEX($C$2:$C$4,MATCH(B$6&B$7,$A$2:$A$4&$B$2:$B$4,0))
B9:D9	B9	=INDEX($D$2:$D$4,MATCH(B$6&B$7,$A$2:$A$4&$B$2:$B$4,0))
B10:D10	B10	=INDEX($F$2:$F$4,MATCH(B$6&B$7,$A$2:$A$4&$B$2:$B$4,0))
B11:D27	B11	=IF(OR(EOMONTH($A11,0)<B$8,$A11>B$9),0,IF(YEAR(B$8)&MONTH(B$8)=YEAR($A11)&MONTH($A11),((EOMONTH($A11,0)-B$8+1)*B$10)/DAY(EOMONTH($A11,0)),IF(YEAR(B$9)&MONTH(B$9)=YEAR($A11)&MONTH($A11),(DAY(B$9)*B$10)/DAY(EOMONTH($A11,0)),B$10)))

 

[spurs50]

Thanks so much for this Z51. I have been away this long weekend but going to try it out. The problem is that I have thousands of rows so wanted to avoid those helper cells and summarize per country only and months.
Is there a way for the only helpers to be months and country? I am going to try and figure it out in the meantime.
Many thanks for your help