I am looking for some formulas that will calculate monthly project forecast totals based on 3 data elements [Start Date, Duration (Months), Weighted $ Value]. The template I have contains rows of project info and contains rolling 12 monthly values across the top with Month 1 being the current Month (e.g. Sep-2013). Would like to have formulas for these monthly columns that would calculate the monthly breakout values based on the 3 data elements. The part I am struggling with is that I would like to apply the amount proportionate to time of the project is contained in each applicable month.

For example, in the 1st row in the table below, the project started on Sep 14, 2013 and the duration in months is 2. I calculate the duration in days to be **52 daily **rate of **$19.23** (1,000/52). I want to apply ONLY the portion (16 days out of 30 total for Sep: 16 * 19.23 = 308) applied to the Sep-2013 column. The for Oct-2013 column, apply the entire 31 days of the month (31 * 19.23 = 596). And for Nov-2013 (the remaining 5 days: 5 * 19.23 = 96).

[TABLE="class: grid, width: 100, align: left"]

C**alc Start Date**

[TD="align: center"]**Wtd.Value ('000s) **[/TD]

**Months**

**Sep-2013**

**Oct-2013**

**Nov-2013**

[TD="align: center"]14-Sep-13[/TD]

[TD="align: center"]1,000[/TD]

2

[/td]308

[/td]596

[/td]96

[/td]

[TD="align: center"]05-Oct-13[/TD]

[TD="align: center"]2,000[/TD]

1

[/td]0

[/td]1,677

[/td]323

[/td]

[TD="align: center"]22-Jul-13[/TD]

[TD="align: center"]500[/TD]

4

[/td]122

[/td]126

[/td]89

[/td]

[/TABLE]

I attached my file to use for reference. Also, another criteria would be IF there is a NULL value in the Calc Start Date column (Col S), then the monthly values should be set to 0.

I hope this makes sense. If this can be done with formulas that would be great. But if it has to be done with VB code, that works as well. Feel free to add any helper columns or rows as needed.

Thank you in advance for your help.