Allocate Value Across Monthly Periods

[AlanR]

Dear All
I would like to do the following via a formula/formulas if possible,
rather than using VBA.
I would like to allocate a given value across its relevant period.
For example, the headings row would be: cell A1 = "value", B1 = "from
date", C1 = "to date", D1 = "Oct-09", E1 = "Nov-09" all the way to P1
= "Oct-10"
Then in row2, A2 = 1200, B2 = "01-Jul-09", C2 = "31-Jun-10".
In cells D2 to P2, I would like to enter formula/formulas to allocate
the 1200 across the period in cells B2 and C2.
The value in cell D2 should return 400 as it is a catchup of 4 months
(Jul-Oct). The values in cells E2 to L2 should each show a value of
100, and the values in cells M2 to P2 should be zero.
Please note:
1) the "from date" and "to date" are not always 12 month intervals,
they could be 1 month, 3 months, etc
2) the value to be allocated in each month should be a standard
monthly amount, not an amount based on the number of days in that
specific month.
3) sometimes the start period would be in the future, so the first
month's allocation might be in say "Dec-09", not "Oct-09".
4) it's fine to add another workings column if that makes life easier
for the final formula. For example an extra column that calculates
the number of months between cells B2 and C2
If anyone can help, that would be much appreciated.
Thanks,
AlanR
p.s. in case it's relevant, I use the Analysis Toolpak add-in.

 

[Jacob Skaria]

Try this formula in D2 and copy across..../down as required

=IF(AND(TEXT($B2,"yyyymm")<=TEXT(D$1,"yyyymm"),TEXT($C2,"yyyymm")>=TEXT(D$1,"yyyymm")),IF(ISNUMBER(C$1),$A2/(DATEDIF($B2,$C2,"m")+1),(DATEDIF($B2,D$1,"m")+1)*$A2/(DATEDIF($B2,$C2,"m")+1)),0)

If this post helps click Yes