Pro rata formula question

[az168]

Hi, I am new to this forum and have been baffled with a formula that I just can't get.

I have an annual charge of £3,060 that is for the period 15/09/20 to 26/11/20.
Which in effect is 73 days, making the charge £620.50 for that whole period.

But I now want to split it out into months, so in Sept the charge is xxx, October the charge is xxx and November the charges is xxx

What formula do I need to automatically calculate the pro rata charge per month?
Also the months that fall out of that criteria would then show as 'nil'

Any help would be very much appreciated.
Thanks

 

[Dave Patton]

Assuming 620.50 is the correct amount that you wish to allocate.
E2 etc are dates like Sep 1 2020

T201809.xlsm
	A	B	C	D	E	F	G	H
1
2	Amount	Start	End	Total	Sep-20	Oct-20	Nov-20	Dec-20
3	620.50	15-Sep-20	26-Nov-20	73	136.00	263.50	221.00	0.00
2d (2)

Cell Formulas
Range	Formula
D3	D3	=C3-B3+1
E3:H3	E3	=MAX(0, MIN(EOMONTH(E$2, 0), $C3) + 1 - MAX(E$2, $B3))/$D3*$A3

 

[az168]

Assuming 620.50 is the correct amount that you wish to allocate.
E2 etc are dates like Sep 1 2020

T201809.xlsm
	A	B	C	D	E	F	G	H
1
2	Amount	Start	End	Total	Sep-20	Oct-20	Nov-20	Dec-20
3	620.50	15-Sep-20	26-Nov-20	73	136.00	263.50	221.00	0.00
2d (2)

Cell Formulas
Range	Formula
D3	D3	=C3-B3+1
E3:H3	E3	=MAX(0, MIN(EOMONTH(E$2, 0), $C3) + 1 - MAX(E$2, $B3))/$D3*$A3

Thank you so much, that has done the trick nicely.

I've racked my brains to try to figure it out, when all I had to do was ask the experts.

 

[Dave Patton]

Thanks for the feedback.