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
 

syali

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

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

braindiesel

Well-known Member
Joined
Mar 16, 2009
Messages
554
Office Version
  1. 365
  2. 2019
  3. 2010
Platform
  1. Windows
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)
 

syali

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

braindiesel

Well-known Member
Joined
Mar 16, 2009
Messages
554
Office Version
  1. 365
  2. 2019
  3. 2010
Platform
  1. Windows
=IF(AND($D2>=$B2,$D2>=$C2),if(E$1=$D2,$A2,0),<aboveformula>)
 

Amit Tandon

Board Regular
Joined
May 23, 2020
Messages
84
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

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

syali

New Member
Joined
Jun 16, 2020
Messages
12
Office Version
  1. 365

ADVERTISEMENT

=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

Board Regular
Joined
May 23, 2020
Messages
84
Office Version
  1. 365
Platform
  1. Windows
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)
 

syali

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

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

Watch MrExcel Video

Forum statistics

Threads
1,123,265
Messages
5,600,605
Members
414,393
Latest member
Vignesh Mechz

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
Top