Break Revenue Down By Month - Start End Date and Amount

footballdj123

New Member
Joined
Apr 5, 2018
Messages
8
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?

Start DateEnd DateAmount4/1/20185/1/20186/1/20187/1/20188/1/20189/1/201810/1/201811/1/201812/1/20181/1/20192/1/20193/1/20194/1/20195/1/20196/1/20197/1/2019
5/18/20186/15/20184250$0$2,125$2,125$0$0$0$0$0$0$0$0$0$0$0$0$0
11/19/201812/16/20180$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0$0
1/14/20195/5/201925200$0$0$0$0$0$0$0$0$0$4,086$6,357$7,038$6,811$908$0$0
1/9/20192/17/201922500$0$0$0$0$0$0$0$0$0$13,269$9,231$0$0$0$0$0
1/9/20192/17/201922500$0$0$0$0$0$0$0$0$0$13,269$9,231$0$0$0$0$0
1/7/201912/30/201949800$0$0$0$0$0$0$0$0$0$3,487$3,906$4,324$4,185$4,324$4,185$4,324

<colgroup><col span="2"><col><col span="6"><col span="3"><col span="7"></colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,393
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,519
Messages
5,523,356
Members
409,513
Latest member
TibiBenyi

This Week's Hot Topics

Top