Calculate # of weeks NTE Budget

[dawnmau]

Premise: Units budgeted is in weeks
would like to auto fill cells based on comparing Start date and Finish Date to the Month Date.


if P5 <> C17:D17, then calculate # of weeks from C17(Start Date) and P5 (Month End Date). ' ie, Month End 5-10 s/b 3 and Month End 6-10 s/b 4

I will need to have cell validated to not exceed 4 (4 full weeks per month)

I tried doing a calculation using the Roundup and Datedif function in combination with an "If, Then" statement to basically say, If the Start Date is after the MonthEnd date then 0, otherwise Roundup((Datedif(Start Date, MonthEnd Date, "d")/7),0) but I need a way to make sure that the sum of the row does not exceed the budget.

Any suggestions? (I keep getting #NUM, #VALUE for Apr-10)

File attached