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

 

[braindiesel]

How is this....

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
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	A	120	1-Jan	31-Dec	1-Jan	$ 10.00	$ 10.00	$ 10.00	$ 10.00	$ 10.00	$ 10.00	$ 10.00	$ 10.00	$ 10.00	$ 10.00	$ 10.00	$ 10.00
3	B	120	1-Jan	31-Dec	1-Mar	$ -	$ -	$ 30.00	$ 10.00	$ 10.00	$ 10.00	$ 10.00	$ 10.00	$ 10.00	$ 10.00	$ 10.00	$ 10.00
Sheet2

Cell Formulas
Range	Formula
F2:F3	F2	=IF($E2<=F$1,COUNTIF($E2:E2,0)*$B2/12+$B2/12,0)
G2:Q3	G2	=IF($E2<=G$1,COLUMNS($F$1:G$1)*$B2/12-SUM($F2:F2),0)

 

[syali]

I was hoping for one formula that took account both occurrences as I may have a list of charges with different charge dates and invoice dates

 

[braindiesel]

I dont understand what you mean... each line looks at that line and produces the exact result you were typing in manually.
It will work for as many lines with varying dates and amounts as you have... line by line.
Is your concern that there are two formulas in the solution? One for the first and one for the rest?

 

[syali]

Yeah I was hoping for one formula. That way I can extra the revenue data and with the formula already created nothing will have to be changed. Hope that makes sense. Apologies for not being clear

 

[braindiesel]

Often you have to accept two formulas... this seems like one of those times.

 

[braindiesel]

On a walk last night, I thought about your request... this would likely work as a one formula option...
In cell F2
=if(Column(f2)=column($F$1),IF($E2<=F$1,COUNTIF($E2:E2,0)*$B2/12+$B2/12,0),IF($E2<=G$1,COLUMNS($F$1:G$1)*$B2/12-SUM($F2:F2),0))

 

[syali]

Thanks for this. I have come across another stumbling block.. I don't think I was very clear with my explanations.

I currently have various charges with various date ranges and invoice dates

I need the charges to be split equally into the correct month
(the tricky part) If the the charge date range is before the invoice date the backdated charges will need to be placed in the current month of the invoice so the future charges are correct.

e.g If the invoice is for £500 and dated 01/04/20 with the charge from and to date of 01/01/2020-01/05/2020 I will need £400 to appear in April column and £100 to appear in May column.

The below is with no formulas as an example

Was hoping for one formula which did both the above, that way I can drag the formula down. Hope this makes sense

 

[Amit Tandon]

26Aug19.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		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
2	A	3762	4/1/2020	4/30/2020	4/1/2020	0	0	0	3762	0	0	0	0	0	0	0	0
3	B	500	1/1/2020	5/31/2020	4/1/2020	0	0	0	400	100	0	0	0	0	0	0	0
4	C	750	4/1/2020	6/30/2020	4/1/2020	0	0	0	250	250	250	0	0	0	0	0	0
5	D	2750	4/1/2020	4/30/2020	4/1/2020	0	0	0	2750	0	0	0	0	0	0	0	0
Sheet2

Cell Formulas
Range	Formula
F2:Q5	F2	=((F$1>=$E2)*(F$1<=$D2)+(F$1=$E2)*ROUNDUP(YEARFRAC($C2,$E2)*12,0))*$B2/ROUNDUP(YEARFRAC($C2,$D2)*12,0)

 

[Amit Tandon]

26Aug19.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		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
2	A	3762	4/1/2020	4/30/2020	4/1/2020	0	0	0	3762	0	0	0	0	0	0	0	0
3	B	500	1/1/2020	5/31/2020	4/1/2020	0	0	0	400	100	0	0	0	0	0	0	0
4	C	750	4/1/2020	6/30/2020	4/1/2020	0	0	0	250	250	250	0	0	0	0	0	0
5	D	2750	4/1/2020	4/30/2020	4/1/2020	0	0	0	2750	0	0	0	0	0	0	0	0
Sheet2

Cell Formulas
Range	Formula
F2:Q5	F2	=((F$1>=$E2)*(F$1<=$D2)+(F$1=$E2)*ROUNDUP(YEARFRAC($C2,$E2)*12,0))*$B2/ROUNDUP(YEARFRAC($C2,$D2)*12,0)

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.