Revenue split by month

syali

New Member
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

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

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
Often you have to accept two formulas... this seems like one of those times.

braindiesel

Well-known Member

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

Board Regular
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
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.

Replies
3
Views
129
Replies
5
Views
110
Replies
5
Views
59
Replies
3
Views
202
Replies
8
Views
309

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.

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

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