Charge Distribution over Months...

[PaulF]

Hello all!!

I'm working on some bids that have a very complicated schedule or activities and funding. Upper management wants to see out of $$$ across the months to ensure our milestones and billing covers what we are spending properly...

It would be easy if each phase started and ended on the 1st and EOMonth.... but they don't... So the formula needs to take that into account... I'm not sure why this kicked my butt so hard but it did... I have a functioning formula, but it is ugly and too long...

I'm guessing there is an easier way:

=IF(OR(YEAR(W$2)*100+MONTH(W$2)<YEAR($E6)*100+MONTH($E6),YEAR(W$2)*100+MONTH(W$2)>YEAR($F6)*100+MONTH($F6)),"",IF(AND(MONTH(W$2)=MONTH($E6),YEAR(W$2)=YEAR($E6)),(EOMONTH($E6,0)-$E6)*($G6/($F6-$E6)),IF(AND(MONTH(W$2)=MONTH($F6),YEAR(W$2)=YEAR($F6)),DAY($F6)*($G6/($F6-$E6)),DAY(EOMONTH(W$2,0))*($G6/($F6-$E6)))))

 

[Hui]

I3: =IF(AND($E3<J$2,$F3>=I$2),$G3*IF($F3<J$2,($F3-I$2+1)/($F3-$E3),(J$2-$E3-1)/($F3-$E3)),0)
Copy across and down

Change the Cell Formatting to: $#,###.0,k;;

 

[PaulF]

I3: =IF(AND($E3<J$2,$F3>=I$2),$G3*IF($F3<J$2,($F3-I$2+1)/($F3-$E3),(J$2-$E3-1)/($F3-$E3)),0)
Copy across and down

Change the Cell Formatting to: $#,###.0,k;;

Hui, Thank you sir the quick reply... Your formula works for row one, then the results get wonky... I did update my Cell Formatting, ty...

 

[bosco_yip]

Hi Paul,

1] Confirm your duration days,

generally Duration =End-Start+1,

but your calculation seem Duration =End-Start

2] Then, based on Duration =End-Start+1,

in I3, formula copy across and down :

=MAX(0,MIN(EOMONTH(I$2,0),$F3)-MAX(I$2,$E3)+1)/($F3-$E3+1)*$G3

Regards
Bosco

 

[PaulF]

View attachment 42113

Hi Paul,

1] Confirm your duration days,

generally Duration =End-Start+1,

but your calculation seem Duration =End-Start

2] Then, based on Duration =End-Start+1,

in I3, formula copy across and down :

=MAX(0,MIN(EOMONTH(I$2,0),$F3)-MAX(I$2,$E3)+1)/($F3-$E3+1)*$G3

Regards
Bosco

10 times simpler and more accurate... Thank you sir...