Excel If Function using dates and calculating values

JohnnyhH - Feb 11, 2015 at 04:10 PM
I'm trying populate sales by quarter with rows of business opportunities. I have a column for "Revenue" then a column for "number of Months" 12 if it's a 1 year contract, 60 if it's a 5 year contract. Then I have a "Start Date"
Revenue Duration Start Date 1stQtr 2ndQtr 3rdQtr 4thQtr
Task 1 $1,000,000 12 May 1, 2015 $0

So there should be 3 if statements for each month in a Quarter. In this case the work starts on May 1st so the value in the 1st Quarter should return as $0. The second quarter should return a value of 2/12 of $1,000,000 since only 2 months in that quarter get revenue. So I guess after the If statements the formula should calculate Revenue ($1,000,000) / Months (12) X how many months in that quarter receive Revenue.

So Qtr 2=$1,666,667 (2 months) Qtrs 3,4 & 1sts Qtr 2016 = $250,000 and 2nd Qtr in 2016 = $83,333 (1 month)