IF Statement with a Value Unchanged

[s1oop]

So I have a couple cells, 2 of them are dates of contract starts and ends. I then have cells which contain the current Trade Up Quote that changes every 3 months.

I want to create an IF statement within the cells that contain the Trade Up Quote values that shows when the contract end date is less than Today(), it will show contract ended in the Trade Up Quote Box.


So if my contract start is 5/1/2009 and the end is 5/1/2012, and the trade up is 1400. I have =IF(M4<TODAY(),"CONTRACT value do a p section there.< in statement if the with P5 of enter i how P5, false reference circular create If false. is it 0.00 leaves which Ended?,??),>
So if p5 is 1400, with m4 is 5/1/2012, i want p5 to stay 1400 until 5/1/2012 passes and then it changes to contract ended.

Please advise with how I enter the value into p5 with the if statement as a circular reference, because now i can get the if statement in without knowing how to just keep the 1400 in p5. This would all be in p5 obviously.

 

[AlphaFrog]

Cell P5 cannot be a constant (1400) and a formula(=IF...) at the same time. You could put the constant within a formula...
=IF(M5>TODAY(), 1400, "End Contract")

Or you could put something like this in cell Q5
=IF(M5>TODAY(), "", "End Contract")

You could also use Conditional Formatting to "white out" the font color of P5 after the date criteria...
Condition 1:
Formula is =M5< Today()
White Font color.

 

[s1oop]

Thanks for the response.

If I have a section with a date where quotes are valid until (every quarter). Lets say 09/30/2011. A section with the quarterly payment. Lets say 500. And the Quote section that is reduced by 500 when the payment is made every quarter.

How would I setup the Valid Until section to automatically update itself in 3 month increments once a payment has been made, as well as change the Quote section to subtract the 500.

So i need 2 sections to update, and they kind of work together in terms of dates

See this attached example: http://cl.ly/083w3c3K3u3D310e3J0W

 

[s1oop]

I can't seem to get the conditional formatting for the Time Left section to correspond to the dates in the Contract End section. Should it not be M column < Today() formats O column with whatever.