Spread revenue across month

[manshah]

Hi
New user here!

I want to spread the revenues across the months. I saw the earlier post and used the formula (=IF(OR(D$1<=$A2-DAY($A2),D$1>EOMONTH($B2,0)),"",MAX(MIN(EDATE(D$1,1)-$A2,EDATE(D$1,1)-D$1,$B2-D$1+1,$B2-$A2+1),0)*$C2/($B2-$A2+1)) but it does not work if one of the date is missing.

Below please see my sample data and expected result.

[TABLE="width: 1290"]
<tbody>[TR]
[TD="colspan: 2"]Start Date[/TD]
[TD]End Date[/TD]
[TD][/TD]
[TD]Amount[/TD]
[TD][/TD]
[TD="align: right"]Jun-19[/TD]
[TD][/TD]
[TD="align: right"]Jul-19[/TD]
[TD][/TD]
[TD="align: right"]Aug-19[/TD]
[TD][/TD]
[TD="align: right"]Sep-19[/TD]
[TD][/TD]
[TD="align: right"]Oct-19[/TD]
[TD][/TD]
[TD="align: right"]Nov-19[/TD]
[TD][/TD]
[TD="align: right"]Dec-19[/TD]
[/TR]
[TR]
[TD]6/15/2019[/TD]
[TD][/TD]
[TD]6/15/2019[/TD]
[TD][/TD]
[TD] $ 5,000.00[/TD]
[TD][/TD]
[TD] $ 5,000.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7/30/2019[/TD]
[TD][/TD]
[TD]8/31/2019[/TD]
[TD][/TD]
[TD] $ 4,000.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 242.42[/TD]
[TD][/TD]
[TD] $ 3,757.58[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7/31/2019[/TD]
[TD][/TD]
[TD]8/30/2019[/TD]
[TD][/TD]
[TD] $ 2,000.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 64.52[/TD]
[TD][/TD]
[TD] $ 1,935.48[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9/1/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 1,000.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 1,000.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]8/26/2019[/TD]
[TD][/TD]
[TD] $ 1,000.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 1,000.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9/6/2019[/TD]
[TD][/TD]
[TD]12/31/2019[/TD]
[TD][/TD]
[TD] $ 4,500.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 961.54[/TD]
[TD][/TD]
[TD] $ 1,192.31[/TD]
[TD][/TD]
[TD] $ 1,153.85[/TD]
[TD][/TD]
[TD] $ 1,192.31[/TD]
[/TR]
</tbody>[/TABLE]



I would really appreciate if any one can suggest me a formula which takes into consideration the blank dates and pulls the values.

Thank you!

 

[Rijnsent]

Hi new user ,
quick&dirty: I guess you could replace $A2 by IF($A2="",$B2,$A2) and replace $B2 by IF($B2="",$A2,$B2).
Like so:
(=IF(OR(D$1<=IF($A2="",$B2,$A2)-DAY(IF($A2="",$B2,$A2)),D$1>EOMONTH(IF($B2="",$A2,$B2),0)),"",MAX(MIN(EDATE(D$1,1)-IF($A2="",$B2,$A2),EDATE(D$1,1)-D$1,IF($B2="",$A2,$B2)-D$1+1,IF($B2="",$A2,$B2)-IF($A2="",$B2,$A2)+1),0)*$C2/(IF($B2="",$A2,$B2)-IF($A2="",$B2,$A2)+1))
Does that work?
Cheers,
Koen

 

[manshah]

This is fabulous. Yep it works. Thank you so much for your help!!

 

[manshah]

Hey
I got another situation where there is another column called Expected close date. So if Rev Start Date and End Date is blank it will look up to Expected Close month and spread the revenue respectively. Please see the row in color in the sample data with expected result below. How can I update your formula to reflect this change?

[TABLE="width: 1579"]
<colgroup><col><col><col><col><col><col><col><col><col span="13"></colgroup><tbody>[TR]
[TD="colspan: 2"]<nobr>Start Date</nobr>[/TD]
[TD]End Date[/TD]
[TD] [/TD]
[TD]Expected Close Date[/TD]
[TD] [/TD]
[TD]Amount[/TD]
[TD] [/TD]
[TD]19-Jun[/TD]
[TD] [/TD]
[TD]19-Jul[/TD]
[TD] [/TD]
[TD]19-Aug[/TD]
[TD] [/TD]
[TD]19-Sep[/TD]
[TD] [/TD]
[TD]19-Oct[/TD]
[TD] [/TD]
[TD]19-Nov[/TD]
[TD] [/TD]
[TD]19-Dec[/TD]
[/TR]
[TR]
[TD="align: right"]6/15/2019[/TD]
[TD] [/TD]
[TD="align: right"]6/15/2019[/TD]
[TD] [/TD]
[TD="align: right"]6/10/2019[/TD]
[TD] [/TD]
[TD="align: right"]$5,000.00[/TD]
[TD] [/TD]
[TD="align: right"]$5,000.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]7/30/2019[/TD]
[TD] [/TD]
[TD="align: right"]8/31/2019[/TD]
[TD] [/TD]
[TD="align: right"]8/10/2019[/TD]
[TD] [/TD]
[TD="align: right"]$4,000.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]$242.42[/TD]
[TD] [/TD]
[TD="align: right"]$3,757.58[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]7/31/2019[/TD]
[TD] [/TD]
[TD="align: right"]8/30/2019[/TD]
[TD] [/TD]
[TD="align: right"]8/10/2019[/TD]
[TD] [/TD]
[TD="align: right"]$2,000.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]$64.52[/TD]
[TD] [/TD]
[TD="align: right"]$1,935.48[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]9/1/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]9/10/2019[/TD]
[TD] [/TD]
[TD="align: right"]$1,000.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]$1,000.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]8/26/2019[/TD]
[TD] [/TD]
[TD="align: right"]8/10/2019[/TD]
[TD] [/TD]
[TD="align: right"]$1,000.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]$1,000.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]9/6/2019[/TD]
[TD] [/TD]
[TD="align: right"]12/31/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]$4,500.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]$961.54[/TD]
[TD] [/TD]
[TD="align: right"]$1,192.31[/TD]
[TD] [/TD]
[TD="align: right"]$1,153.85[/TD]
[TD] [/TD]
[TD="align: right"]$1,192.31[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]6/10/2019[/TD]
[TD] [/TD]
[TD="align: right"]$5,000.00[/TD]
[TD] [/TD]
[TD="align: right"]$5,000.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]