Depending on what accuracy you want the answer to be in, you can
calculate the contract time in either days or months. Let's start with
days:
'Date format = mm/dd/yyyy
'Excel will use Julian date format for calculations
Contract_End_Time = 8/31/2007
Contract_Start_Time = 10/01/2005
Contract_Value_Total = 36000
Contract_Length = Contract_End_Time - Contract_Start_Time
Contract_Value_per_Day = Contract_Value_Total / Contract_Length
Monthly_Budget = Contract_Value_per_Day * 30 '(Here you may have to get
elegant if required, to work on different days in a month)
-------------------------
If you want to work on a monthly basis (my preference), change the last
line of logic to the following:
Contract_Value_per_Month = Contract_Value_Total / Contract_Length *
365.25 / 12 '(365 days per year, adjusted to include leap years, and 12
months in a year)
To see whether a month in your budget year falls within the contract
period, use an IF statement as follows:
IF(Evaluated_Month > Contract_Start_Time) AND IF(Evaluated_Month <
Contract_End_Time)
THEN
Do all the maths
ELSE ZERO.