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
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​
Any update please?
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

syali

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

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

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

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), 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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,260
Messages
5,600,586
Members
414,391
Latest member
Pandurang

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