Monthly Allocation Based on Daily Amount

[Hunter Childress]

Hello,

Please see the attached file. I need a formula to allocate the total amount based on the daily pull rate across the months in yellow.

For Example on Job 1, If the job starts on 8/26, the daily pull rate should only be allocated for the days remaining in August, then continue to September and so forth.

Thanks,

 

[Chihiro]

Here. Follow steps outlined in link (adjust either H2:K2 value or formula as needed).

http://www.excel-university.com/excel-formula-to-allocate-an-amount-into-monthly-columns/

 

[Hunter Childress]

I tried this formula previously and it doesn't work for historical dates because the last part of the formula shown in blue =TRUE for historical dates which equals 1 so it adds one more allocated day to the final result.

For Dates in the future, this formula works but not with historical dates.

EXAMPLE FROM LINK PROVIDED:
$G9*
((MAX(H$8-$C9,0)-MAX(EOMONTH(H$8,-1)-$C9,0))
-(MAX(H$8-$D9,0)-MAX(EOMONTH(H$8,-1)-$D9,0))
+(EOMONTH(H$8,0)=EOMONTH($C9,0)))

 

[Hunter Childress]

Attached is the file with the formula to show it doesn't work.

 

[Chihiro]

Really, it's just simple adaptation... the principle applies regardless.

See modified formula.

=((MAX(EOMONTH(H$2,0)-$C3,0)-MAX(EOMONTH(H$2,-1)-$C3,0))-(MAX(EOMONTH(H$2,0)-$D3,0)-MAX(EOMONTH(H$2,-1)-$D3,0)))*$E3

 

[Hunter Childress]

Ahh... that make sense. Thank you very much! I really appreciate it.