build a Formula in excel

[harry]

Hi All,

I am trying to populate the status of a contract . based on its expiry date.

For example :- in cell N with the heading Contract Expiry Date , now i would like to build a formula which can provide me the following result

It should alert me 30 days in advance from the date of expiration of contract with a text saying"renew the contract"
It should alert me 60 days in advance from the date of expiration of contract with text saying "expiring soon"
It should tell me if the contract is under contract of expired
It should return the blank cell as blank

Thank you for your help

[pecoflyer]

I have put this formula =IF(N12="","",IF(N12>TODAY()," In-Contract",IF(N12<=TODAY()+60,"Expiring Soon",IF(N12<=TODAY()+30,"Renew","Expired"))))

however its not giving accurate answer
Can some one check and assist me what did i do wrong

Plz help me at the earlist
Please help me i am waiting to hear from you

[ManxXL]

Hi Harry,

It looks like the order of your IF statement is the problem, with the order you currently have, the statement will end as long as the date is greater than todays date, so the formula will not check for today + 30 or today + 60 The below should give you what you want.

Code: Select all

=IF(N12="","",IF(N12<TODAY()," Expired",IF(N12<=TODAY()+30,"Renew",IF(N12<=TODAY()+60,"Expiring Soon","In Contract"))))