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.

Start Date	End Date		Amount		Jun-19		Jul-19		Aug-19		Sep-19		Oct-19		Nov-19		Dec-19
6/15/2019		6/15/2019		$ 5,000.00		$ 5,000.00
7/30/2019		8/31/2019		$ 4,000.00				$ 242.42		$ 3,757.58
7/31/2019		8/30/2019		$ 2,000.00				$ 64.52		$ 1,935.48
9/1/2019				$ 1,000.00								$ 1,000.00
		8/26/2019		$ 1,000.00						$ 1,000.00
9/6/2019		12/31/2019		$ 4,500.00								$ 961.54		$ 1,192.31		$ 1,153.85		$ 1,192.31

<tbody>
</tbody>

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?

<nobr>Start Date</nobr>	End Date		Expected Close Date		Amount		19-Jun		19-Jul		19-Aug		19-Sep		19-Oct		19-Nov		19-Dec
6/15/2019		6/15/2019		6/10/2019		$5,000.00		$5,000.00
7/30/2019		8/31/2019		8/10/2019		$4,000.00				$242.42		$3,757.58
7/31/2019		8/30/2019		8/10/2019		$2,000.00				$64.52		$1,935.48
9/1/2019				9/10/2019		$1,000.00								$1,000.00
		8/26/2019		8/10/2019		$1,000.00						$1,000.00
9/6/2019		12/31/2019				$4,500.00								$961.54		$1,192.31		$1,153.85		$1,192.31
				6/10/2019		$5,000.00		$5,000.00

<colgroup><col><col><col><col><col><col><col><col><col span="13"></colgroup><tbody>
</tbody>