I am trying to create an IF Statement that based off the start date, life of the contract and Prepaid amount it will populate accordingly in the correct month.
Example: start date is 7/1/2017, amount is $911.97 and life of contract is 3 months. How do I get each month to reflect the correct monthly cost for that month if any, based on the life of the contract?
If just for 3 months Oct and on should have zeroes.
Example:
THANKS FOR YOUR HELP!
Based on what I understand, I suggest a solution that requires you to create valid dates in the Month Row rather than putting Text MMM (Jan, Feb, Mar etc). It will be a lot more easier this way. You just need to format it as MMM-YY so that it will be shown as Jul-17, Aug-17 and so on. This will make it more convenient when the next year starts.
I am still not clear the purpose of hidden column O as seen in your formula. This solution does not require it though.
In this example F7 holds Start Date, G7 holds Amount I7 holds Number of Months and Monthly Amount is in J7. R7 onward is the monthly split.
In R7 put the following formula
=IF(AND(MONTH($F$7)=MONTH(R6),YEAR($F$7)=YEAR(R6)),$J$7,0)
R7 is the starting point. There's nothing before it, hence it's a two formula solution.
Now in S7 put the following formula and drag it across horizontally till intended columns. Note that as mentioned earlier I assume that you have valid dates in format MMM-YY in Row 6 R6 onward for this to work.
=IF(R7=0,IF(AND(MONTH($F$7)=MONTH(S6),YEAR($F$7)=YEAR(S6)),$J$7,0),IF(COUNTIF($R$7:R7,"<>0")<$I$7,$J$7,0))
Now put your data in the input cells and test the solution. You can extend it to next year as well. Just create Valid MMM-YY data in row 6.
