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]

This presumes there are valid dates in columns C, D & E and in the 1st row F1:Q1 and dates in column C & E & in the 1st row are the 1st of the month and dates in column D are the last date of the month.

Thanks for this! Is it possible to have it without the presuming that dates in column C & D will always be first and last of the month? This is because the charge dates always vary

 

[braindiesel]

How is this

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Charge	Charge from	Charge to	Invoice date	1-Jan	1-Feb	1-Mar	1-Apr	1-May	1-Jun	1-Jul	1-Aug	1-Sep	1-Oct	1-Nov	1-Dec
2	120	1-Jan	31-Dec	1-Jan	10	10	10	10	10	10	10	10	10	10	10	10
3	120	1-Feb	31-Mar	1-Mar	0	0	120	0	0	0	0	0	0	0	0	0
4	120	1-Jan	31-May	1-Apr	0	0	0	96	24	0	0	0	0	0	0	0
5	120	1-May	31-Oct	1-Jul	0	0	0	0	0	0	60	20	20	20	0	0
6	120	1-Jun	31-Aug	1-Jun	0	0	0	0	0	40	40	40	0	0	0	0
Sheet1

Cell Formulas
Range	Formula
E2:P6	E2	=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)

 

[syali]

That's great! Nearly there! I think we are getting much closer now, however it was not working when the charge range is less than the invoice date:

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​

120​

01/01/2020​

31/03/2020​

01/04/2020​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

120​

15/04/2020​

15/04/2020​

01/04/2020​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

 

[braindiesel]

=IF(AND($D2>=$B2,$D2>=$C2),if(E$1=$D2,$A2,0),<aboveformula>)

 

[Amit Tandon]

Thanks for this! Is it possible to have it without the presuming that dates in column C & D will always be first and last of the month? This is because the charge dates always vary

Enter below in cell F2 and copy to the right and down. It will also account for multiple months / years as per dates and will consider the month for each date so that column C/D/E and 1st row can have any date within a month.

=(((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,EOMONTH($E2,-1)+1)*12, 0))*$B2/ROUNDUP(YEARFRAC(EOMONTH($C2,-1)+1, EOMONTH($D2, 0))*12,0)

 

[syali]

Enter below in cell F2 and copy to the right and down. It will also account for multiple months / years as per dates and will consider the month for each date so that column C/D/E and 1st row can have any date within a month.

=(((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,EOMONTH($E2,-1)+1)*12, 0))*$B2/ROUNDUP(YEARFRAC(EOMONTH($C2,-1)+1, EOMONTH($D2, 0))*12,0)

Thats great! It has worked for most but is doubling the charge when the charge date is less than invoice date:

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/01/2020​

31/12/2020​

01/03/2020​

0​

0​

30​

10​

10​

10​

10​

10​

10​

10​

10​

10​

B

120​

28/02/2020​

28/02/2020​

01/04/2020​

0​

0​

0​

240​

0​

0​

0​

0​

0​

0​

0​

0​

 

[syali]

=IF(AND($D2>=$B2,$D2>=$C2),if(E$1=$D2,$A2,0),<aboveformula>)

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]

Thats great! It has worked for most but is doubling the charge when the charge date is less than invoice date:

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/01/2020​

31/12/2020​

01/03/2020​

0​

0​

30​

10​

10​

10​

10​

10​

10​

10​

10​

10​

B

120​

28/02/2020​

28/02/2020​

01/04/2020​

0​

0​

0​

240​

0​

0​

0​

0​

0​

0​

0​

0​

Not accounted for the possibility of Charge Date to be less than Invoice date ... was based on the condition that Invoice Date will always fall inbetween. Anyways, updated the cell F2 formula to:

=(((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))*$B2/ROUNDUP( YEARFRAC(EOMONTH($C2, -1)+1, EOMONTH($D2,0))*12,0)

 

[syali]

Not accounted for the possibility of Charge Date to be less than Invoice date ... was based on the condition that Invoice Date will always fall inbetween. Anyways, updated the cell F2 formula to:

=(((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))*$B2/ROUNDUP( YEARFRAC(EOMONTH($C2, -1)+1, EOMONTH($D2,0))*12,0)

Thank you so much, I think it has worked!!!

 

[syali]

Not accounted for the possibility of Charge Date to be less than Invoice date ... was based on the condition that Invoice Date will always fall inbetween. Anyways, updated the cell F2 formula to:

=(((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))*$B2/ROUNDUP( YEARFRAC(EOMONTH($C2, -1)+1, EOMONTH($D2,0))*12,0)

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​