Revenue split by month

[syali]

Hi,

I have the total charge along with the start and end dates and need it spread over months.

Scenario A is pretty simple where all costs have been allocated evenly

Scenario B if the revenue start date is less than the invoice date I need it to calculate the total back dated charge and include it in the current invoice month with the rest spread equally

I have included example below where I have hard coded the values. It would be really useful if one formula could calculate this?

	Charge	Charge from	Charge to	Invoice date	Jan-20​	Feb-20​	Mar-20​	Apr-20​	May-20​	Jun-20​	Jul-20​	Aug-20​	Sep-20​	Oct-20​	Nov-20​	Dec-20​
A	120​	01/01/2020​	31/12/2020​	01/01/2020​	10.00	10.00	10.00	10.00	10.00	10.00	10.00	10.00	10.00	10.00	10.00	10.00
B	120​	01/01/2020​	31/12/2020​	01/03/2020​	-	-	30.00	10.00	10.00	10.00	10.00	10.00	10.00	10.00	10.00	10.00

 

[syali]

Sorry. its not working when the invoice date is lower than the charge dates. I would expect the charges to fall in June in the below example. Maybe will have to calculate by days?

Charge

Charge from

Charge to

Invoice date

01/01/2020​

01/02/2020​

01/03/2020​

01/04/2020​

01/05/2020​

01/06/2020​

01/07/2020​

01/08/2020​

01/09/2020​

01/10/2020​

01/11/2020​

01/12/2020​

A

120​

01/06/2020​

30/06/2020​

01/05/2020​

0​

0​

0​

0​

240​

120​

0​

0​

0​

0​

0​

0​

Any update please?

 

[braindiesel]

I answered this above already

 

[syali]

I answered this above already

Sorry, is this the full formula?

Is this the full formula:

=IF(AND(E$1>=$B3,E$1<=$C3),IF(E$1<$D3,0,$A3/(MONTH($C3)-MONTH($B3)+1)*IF(E$1=$D3,(MONTH($D3)-MONTH($B3)+1),1)),IF(AND($D3>=$B3,$D3>=$C3),IF(E$1=$D3,$A3,0)))

 

[Amit Tandon]

Sorry. its not working when the invoice date is lower than the charge dates. I would expect the charges to fall in June in the below example. Maybe will have to calculate by days?

Charge

Charge from

Charge to

Invoice date

01/01/2020​

01/02/2020​

01/03/2020​

01/04/2020​

01/05/2020​

01/06/2020​

01/07/2020​

01/08/2020​

01/09/2020​

01/10/2020​

01/11/2020​

01/12/2020​

A

120​

01/06/2020​

30/06/2020​

01/05/2020​

0​

0​

0​

0​

240​

120​

0​

0​

0​

0​

0​

0​

This should cover:

=(((EOMONTH(F$1,-1)+1)>=(EOMONTH($E2,-1)+1))*((EOMONTH(F$1,-1)+1)<=(EOMONTH($D2,0)))+((EOMONTH(F$1,-1)+1)=(EOMONTH($E2,-1)+1))*ROUNDUP(YEARFRAC(EOMONTH($C2,-1)+1,MIN(EOMONTH($E2,-1)+1,EOMONTH($D2,0)))*12,0))*((EOMONTH(F$1,-1)+1)>=(EOMONTH($C2,-1)+1))*$B2/ROUNDUP(YEARFRAC(EOMONTH($C2,-1)+1,EOMONTH($D2,0))*12,0)

 

[braindiesel]

=IF(AND($D2>=$B2,$D2>=$C2),if(E$1=$D2,$A2,0), IF(AND(E$1>=$B2,E$1<=$C2),IF(E$1<$D2,0,$A2/(MONTH($C2)-MONTH($B2)+1)*IF(E$1=$D2,(MONTH($D2)-MONTH($B2)+1),1)),0))
This is what I had proposed last week or so... you may need to move the targeted cells but I believe this should work.
B2 = Charge from cell
C2 = Charge to cell
D2 = Invoice date
E1 = Month header

If it does not work, use the evaluate formula button to walk through it to see where it goes wrong and let me know