Formula to Allocate an Amount into Monthly Columns

[Bear]

Hi,

Can someone help me on how to do a formula on how to spread the value by using the start date and end date. All contract is last for 3 years with different value for each year.

Example
my start date is under column B (3-Mar-2015)
my end date is under colum C (2-Mar-2018)
Year 1:400
Year 2 :420
Year 3: 440

The answer for this case would be:
Jan-2015= 0
Feb 2015 = 0
Mar 2015 = 374
Apr 2015 = 400

column G to AD is where i need to spread the amount ( Jan-15 to December-16)

Please let me know. I would appreciate it very much

 

[Luke M]

Hi Bear,

THere's probably a more elegant solution, but here's what I came up with.

Formula:

=IF(OR(TEXT(G$2,"yyyymm")>TEXT($C3,"yyyymm"),TEXT(G$2,"yyyymm")<TEXT($B3,"yyyymm")),0,
IF(AND(MONTH(G$2)<>MONTH($B3),MONTH(G$2)<>MONTH($C3)),INDEX($D3:$F3,DATEDIF($B3,G$2,"y")+1),
IF(TEXT($B3,"yyyymm")=TEXT(G$2,"yyyyymm"),(G$1-DAY($B3)+1)/G$1*$D3,
IF(TEXT($C3,"yyyymm")=TEXT(G$2,"yyyymm"),DAY($C3)/G$1*$F3,
(G$1-DAY($B3)+1)/G$1*INDEX($D3:$F3,DATEDIF($B3,G$2,"y")+1)+DAY($C3)/G$1*INDEX($D3:$F3,DATEDIF($B3,G$2,"y")+2)))))

Roughly, here's what each line/IF does:
1) If date it outside range of contract, then output is 0
2) If it's not the start, end, or transition month, then pick appropriate year value
3) If it's the start month, pick first year value, apply ratio
4) If it's the end month, pick last year value, apply ratio
5) Only case left is a transition month, so add sum of appropriate ratios

Note that to help with ratios, I added in line in the worksheet to calculates days per month. You could do this within the formula, if you wanted, but IMO, the formula was already complicated enough.

 

[Bear]

Hi Bear,

THere's probably a more elegant solution, but here's what I came up with.

Formula:

=IF(OR(TEXT(G$2,"yyyymm")>TEXT($C3,"yyyymm"),TEXT(G$2,"yyyymm")<TEXT($B3,"yyyymm")),0,
IF(AND(MONTH(G$2)<>MONTH($B3),MONTH(G$2)<>MONTH($C3)),INDEX($D3:$F3,DATEDIF($B3,G$2,"y")+1),
IF(TEXT($B3,"yyyymm")=TEXT(G$2,"yyyyymm"),(G$1-DAY($B3)+1)/G$1*$D3,
IF(TEXT($C3,"yyyymm")=TEXT(G$2,"yyyymm"),DAY($C3)/G$1*$F3,
(G$1-DAY($B3)+1)/G$1*INDEX($D3:$F3,DATEDIF($B3,G$2,"y")+1)+DAY($C3)/G$1*INDEX($D3:$F3,DATEDIF($B3,G$2,"y")+2)))))

Roughly, here's what each line/IF does:
1) If date it outside range of contract, then output is 0
2) If it's not the start, end, or transition month, then pick appropriate year value
3) If it's the start month, pick first year value, apply ratio
4) If it's the end month, pick last year value, apply ratio
5) Only case left is a transition month, so add sum of appropriate ratios

Note that to help with ratios, I added in line in the worksheet to calculates days per month. You could do this within the formula, if you wanted, but IMO, the formula was already complicated enough.

Hi Luke M

That's exactly what I want and your formulas works perfectly.
Thank you!!