Allocate a value over multiple years based on a specified date range

  • I am looking for help with formulas to take a set value and allocate it over a number of years based on a stated date range. The allocation of the stated value is based on the number of months the project is underway during any particular year. If the project starts on or before the 10th of the month, then then the month the project starts is counted as a full month. If the project starts after the 20th of the month, then the month the project is considered to have started the following month.


    Here is an example of what I need help with:


    [TABLE="width: 500"]

    [tr]


    [TD="align: center"][/TD]
    [TD="align: center"]A[/TD]
    [TD="align: center"]B[/TD]
    [TD="align: center"]C[/TD]
    [TD="align: center"]D[/TD]
    [TD="align: center"]E[/TD]
    [TD="align: center"]F[/TD]
    [TD="align: center"]G[/TD]

    [/tr]


    [tr]


    [TD="align: center"]1[/TD]
    [TD="align: center"]Project Name[/TD]
    [TD="align: center"]Start Date[/TD]
    [TD="align: center"]Finish Date[/TD]
    [TD="align: center"]Value[/TD]
    [TD="align: center"]2017[/TD]
    [TD="align: center"]2018[/TD]
    [TD="align: center"]2019[/TD]

    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td]

    North Shore Building

    [/td]


    [td]

    8/10/17

    [/td]


    [td]

    2/28/18

    [/td]


    [td]

    3,000,000

    [/td]


    [td]

    2,142,857

    [/td]


    [td]

    857,143

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    3

    [/td]


    [td]

    Bainbridge Retail

    [/td]


    [td]

    11/20/17

    [/td]


    [td]

    1/25/19

    [/td]


    [td]

    6,000,000

    [/td]


    [td]

    428,571

    [/td]


    [td]

    5,142,858

    [/td]


    [td]

    428,571

    [/td]


    [/tr]


    [/TABLE]

    Thanks in advance for your help!!