Allocation Based on Date

Hey

In rows 9 and 10 of the attached spread sheet there is a made up allocation for some kind of support. The allocation start date and end date are entered in B and C and the date the invoice is posted is entered into D.

The allocation works fine if the month of entry is only one month early but when it is more than that the allocation shifts and is wrong by the end of the year. In the attachment this can be seen in L10 as it is not following the dates correctly - it should be starting in March even though the allocation posted on the system in January

Can anyone help straighten out that formula. I have had it working by comparing to posting date but when the posting date is after the start date that does not work.

The function needs to be able to do the following:

Allocate based on the number of months in the term (custom function (range) "MonthsInTerm"). If the allocation needs to start on the start date and end on the end date. If the posting date is after the start date then there should be a catchup of the monthly allocation (total/MonthsInTerm) 8 the number of months behind.

Example: 24,000 Starts in Jan ends in Dec posted in March would have a 6000 in march and the rest of the year is 2000 a month

Example2: 9,000 Start in April and and ends in Dec but is posted in Jan. the allocation should have 1000 from April to may and that is it.

i am not sure what i am missing in this function to make that happen