Calculating Expiry Dates

Dear friends,

Please help me, its been so long since the last time I pay attention to my teacher in regards to the =If() Formula.

I have a problem now, I am currently making a report for our Accreditations and Certifications. Now, my boss wants me to make a list of all our accreditations and certifications. He requested me to make a list which will warn us (the word "Renewal Due" in different color") if our Certificates reach 3 months before the expiry.

I hope you got my point, again. I need a formula which will warn me, if our certificates will gonna expired.

For example:
Certificate No. 15-100
Issue Date: July 1, 2008
Expiry Date: June 30, 2011

Now, the Status between July 1, 2008 and March 31, 2011 will be CURRENT
If it will reach April 1, 2011 the Status will change to RENEWAL DUE
If it will reach to July 1, 2011, the status will be EXPIRED

I hope you can help me. This is making me cry...
I know, all of you here are experts. I just wish I maintained my notes from school.

Thanks in advance.


PS. Sorry if I posted this in the wrong thread.

Regards,
shinyell
3 months before the expiry date (April 1, 2011)

Please, rename your thread according to the rules

There are a number of approaches possible here... given the three conditions my own preference would be to use Custom Formatting.

If we assume your expiry date is set in C1 then

D1: =IF(ISNUMBER(C1);(TODAY()<EDATE(C1+1;-3))-(TODAY()>C1);"")

You can then apply a Custom Format to D1 of:

[Red]"CURRENT";[Blue]"EXPIRED";[Color50]"RENEWAL DUE"

shinyell, Welcome to the Board.

As zbor stated there are clear requirements when it comes to assigning titles to threads - this is to aid the search function. I have on this occasion modified your title for you however I would ask that you read through and adhere to the Forum Rules going forward.