Break Revenue Down By Month - Start End Date and Amount

[footballdj123]

Hello,

I have two dates and an amount field, I want to break the revenue into each month. I have a formula

=(IF(AND($B2>=D$1,$A2<=DATE(YEAR(D$1),MONTH(D$1)+1,0)),MIN($B2,DATE(YEAR(D$1),MONTH(D$1)+1,1))-MAX($A2,D$1),0)/($B2-$A2))*$C2

It breaks down the revenue incorrectly across the months. I took the value and look at it daily and it was slightly off. The total amount does match up, just not how its weighted into each month.

Any idea why?

[TABLE="width: 1223"]
<colgroup><col span="2"><col><col span="6"><col span="3"><col span="7"></colgroup><tbody>[TR]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]Amount[/TD]
[TD]4/1/2018[/TD]
[TD]5/1/2018[/TD]
[TD]6/1/2018[/TD]
[TD]7/1/2018[/TD]
[TD]8/1/2018[/TD]
[TD]9/1/2018[/TD]
[TD]10/1/2018[/TD]
[TD]11/1/2018[/TD]
[TD]12/1/2018[/TD]
[TD]1/1/2019[/TD]
[TD]2/1/2019[/TD]
[TD]3/1/2019[/TD]
[TD]4/1/2019[/TD]
[TD]5/1/2019[/TD]
[TD]6/1/2019[/TD]
[TD]7/1/2019[/TD]
[/TR]
[TR]
[TD]5/18/2018[/TD]
[TD]6/15/2018[/TD]
[TD]4250[/TD]
[TD]$0[/TD]
[TD]$2,125[/TD]
[TD]$2,125[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[/TR]
[TR]
[TD]11/19/2018[/TD]
[TD]12/16/2018[/TD]
[TD]0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[/TR]
[TR]
[TD]1/14/2019[/TD]
[TD]5/5/2019[/TD]
[TD]25200[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$4,086[/TD]
[TD]$6,357[/TD]
[TD]$7,038[/TD]
[TD]$6,811[/TD]
[TD]$908[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[/TR]
[TR]
[TD]1/9/2019[/TD]
[TD]2/17/2019[/TD]
[TD]22500[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$13,269[/TD]
[TD]$9,231[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[/TR]
[TR]
[TD]1/9/2019[/TD]
[TD]2/17/2019[/TD]
[TD]22500[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$13,269[/TD]
[TD]$9,231[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[/TR]
[TR]
[TD]1/7/2019[/TD]
[TD]12/30/2019[/TD]
[TD]49800[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$3,487[/TD]
[TD]$3,906[/TD]
[TD]$4,324[/TD]
[TD]$4,185[/TD]
[TD]$4,324[/TD]
[TD]$4,185[/TD]
[TD]$4,324[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Maybe...

=IF(AND($B2>=D$1,$A2<=DATE(YEAR(D$1),MONTH(D$1)+1,0)),MIN($B2,EOMONTH(D$1,0))-MAX($A2,D$1-1),0)*$C2/($B2-$A2)

M.

 

[footballdj123]

Thanks for trying Marcelo but still doesn't work. The months don't match up to if I do it daily.