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
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

braindiesel

Well-known Member
Joined
Mar 16, 2009
Messages
560
Office Version
  1. 365
  2. 2019
  3. 2010
Platform
  1. Windows
How is this....
Book3
ABCDEFGHIJKLMNOPQ
1ChargeCharge fromCharge toInvoice date1-Jan1-Feb1-Mar1-Apr1-May1-Jun1-Jul1-Aug1-Sep1-Oct1-Nov1-Dec
2A1201-Jan31-Dec1-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
3B1201-Jan31-Dec1-Mar $ - $ - $ 30.00 $ 10.00 $ 10.00 $ 10.00 $ 10.00 $ 10.00 $ 10.00 $ 10.00 $ 10.00 $ 10.00
Sheet2
Cell Formulas
RangeFormula
F2:F3F2=IF($E2<=F$1,COUNTIF($E2:E2,0)*$B2/12+$B2/12,0)
G2:Q3G2=IF($E2<=G$1,COLUMNS($F$1:G$1)*$B2/12-SUM($F2:F2),0)
 

syali

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

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

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

ADVERTISEMENT

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

Well-known Member
Joined
Mar 16, 2009
Messages
560
Office Version
  1. 365
  2. 2019
  3. 2010
Platform
  1. Windows
Often you have to accept two formulas... this seems like one of those times.
 

braindiesel

Well-known Member
Joined
Mar 16, 2009
Messages
560
Office Version
  1. 365
  2. 2019
  3. 2010
Platform
  1. Windows

ADVERTISEMENT

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

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

1592483003298.png



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

Amit Tandon

Board Regular
Joined
May 23, 2020
Messages
84
Office Version
  1. 365
Platform
  1. Windows
26Aug19.xlsx
ABCDEFGHIJKLMNOPQ
1ChargeCharge fromCharge toInvoice dateJan-20Feb-20Mar-20Apr-20May-20Jun-20Jul-20Aug-20Sep-20Oct-20Nov-20Dec-20
2A37624/1/20204/30/20204/1/2020000376200000000
3B5001/1/20205/31/20204/1/20200004001000000000
4C7504/1/20206/30/20204/1/2020000250250250000000
5D27504/1/20204/30/20204/1/2020000275000000000
Sheet2
Cell Formulas
RangeFormula
F2:Q5F2=((F$1>=$E2)*(F$1<=$D2)+(F$1=$E2)*ROUNDUP(YEARFRAC($C2,$E2)*12,0))*$B2/ROUNDUP(YEARFRAC($C2,$D2)*12,0)
 

Amit Tandon

Board Regular
Joined
May 23, 2020
Messages
84
Office Version
  1. 365
Platform
  1. Windows
26Aug19.xlsx
ABCDEFGHIJKLMNOPQ
1ChargeCharge fromCharge toInvoice dateJan-20Feb-20Mar-20Apr-20May-20Jun-20Jul-20Aug-20Sep-20Oct-20Nov-20Dec-20
2A37624/1/20204/30/20204/1/2020000376200000000
3B5001/1/20205/31/20204/1/20200004001000000000
4C7504/1/20206/30/20204/1/2020000250250250000000
5D27504/1/20204/30/20204/1/2020000275000000000
Sheet2
Cell Formulas
RangeFormula
F2:Q5F2=((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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,885
Messages
5,627,468
Members
416,250
Latest member
darius_rebelo

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