Dividing total revenue over several months

[keizerkarel]

I have invoices with a certain total amount that I want to spread on a timeline per month. Example:
*Date formatting is the standard here. Feel free to swap it around.

[TABLE="width: 800"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Starting date[/TD]
[TD]Ending date[/TD]
[TD]Total amount[/TD]
[TD]jan-18[/TD]
[TD]feb-18[/TD]
[TD]mar-18[/TD]
[/TR]
[TR]
[TD]Customer A[/TD]
[TD]01-01-2018[/TD]
[TD]31-12-2018[/TD]
[TD]120000[/TD]
[TD]10000[/TD]
[TD]10000[/TD]
[TD]10000[/TD]
[/TR]
[TR]
[TD]Customer B[/TD]
[TD]01-02-2018[/TD]
[TD]31-03-2018[/TD]
[TD]10000[/TD]
[TD]0[/TD]
[TD]5000[/TD]
[TD]5000[/TD]
[/TR]
[TR]
[TD]Customer C[/TD]
[TD]01-09-2017[/TD]
[TD]28-02-2018[/TD]
[TD]60000[/TD]
[TD]10000[/TD]
[TD]10000[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

Is there a way to use the total amount + license period to autofill the chart on the right?
- Every month is equal, regardless of amount of days.
- All invoices run in full months from the first day to the last day.
- Invoices can be anywhere between 1 and 24 months.

I'm in doubt whether the above is possible without macro's. My own googling however returned nothing, neither VBA nor excel, that came remotely close.

Anyone who can point me in the right direction?

 

[sheetspread]

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1	Customer	Starting date	Ending date	Total amount	Jan-17	Feb-17	Mar-17	Apr-17	May-17	Jun-17	Jul-17	Aug-17	Sep-17	Oct-17	Nov-17	Dec-17	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18	Jul-18	Aug-18	Sep-18	Oct-18	Nov-18	Dec-18
2	Customer A	1/1/2018	12/31/2018	120000													10000.00	10000.00	10000.00	10000.00	10000.00	10000.00	10000.00	10000.00	10000.00	10000.00	10000.00	10000.00
3	Customer B	2/1/2018	3/31/2018	10000														5000.00	5000.00
4	Customer C	9/1/2017	2/28/2018	60000									10000.00	10000.00	10000.00	10000.00	10000.00	10000.00
Sheet7

Cell Formulas
Range	Formula
E2	=IF(AND(E$1>=$B2,E$1<=$C2),$D2/(1+DATEDIF($B2,$C2,"m")),"")

 

[mumps]

Try the following macro:

Sub DivideRevenue()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim lColumn As Long
    lColumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
    Dim rDate As Range, rDate2 As Range
    For Each rDate2 In Range("B2:B" & LastRow)
        For Each rDate In Range(Cells(1, 5), Cells(1, lColumn))
            If rDate >= rDate2 And rDate <= rDate2.Offset(0, 1) Then
                Cells(rDate2.Row, rDate.Column) = Range("D" & rDate2.Row) / Abs(Month(Range("C" & rDate2.Row)) - Month(Range("B" & rDate2.Row)) + 1)
            Else
                Cells(rDate2.Row, rDate.Column) = 0
            End If
        Next rDate
    Next rDate2
    Application.ScreenUpdating = True
End Sub