Revenue split by month

syali

New Member
Joined
Jun 16, 2020
Messages
12
Office Version
  1. 365
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?

ChargeCharge fromCharge toInvoice 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.0010.0010.0010.0010.0010.0010.0010.0010.0010.0010.0010.00
B
120​
01/01/2020​
31/12/2020​
01/03/2020​
--30.0010.0010.0010.0010.0010.0010.0010.0010.0010.00
 
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
 
Upvote 0

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
How is this
Book1
ABCDEFGHIJKLMNOP
1ChargeCharge fromCharge toInvoice date1-Jan1-Feb1-Mar1-Apr1-May1-Jun1-Jul1-Aug1-Sep1-Oct1-Nov1-Dec
21201-Jan31-Dec1-Jan101010101010101010101010
31201-Feb31-Mar1-Mar00120000000000
41201-Jan31-May1-Apr00096240000000
51201-May31-Oct1-Jul0000006020202000
61201-Jun31-Aug1-Jun000004040400000
Sheet1
Cell Formulas
RangeFormula
E2:P6E2=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)
 
Upvote 0
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:

ChargeCharge fromCharge toInvoice 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​
 
Upvote 0
=IF(AND($D2>=$B2,$D2>=$C2),if(E$1=$D2,$A2,0),<aboveformula>)
 
Upvote 0
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)
 
Upvote 0
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:

ChargeCharge fromCharge toInvoice 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​
 
Upvote 0
=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)))
 
Upvote 0
Thats great! It has worked for most but is doubling the charge when the charge date is less than invoice date:

ChargeCharge fromCharge toInvoice 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)
 
Upvote 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)

Thank you so much, I think it has worked!!!
 
Upvote 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)

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?

ChargeCharge fromCharge toInvoice 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​
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top