Hi, I am looking to write If statement that if I write a date into cell A2, then A1 will display "Good" if its within 4 years since the date in A2, or A1 will display "Expiring Soon" if its between 4-5 years after A2, or A1 will display "Expired" if its 5 years after the date in A2. I have never used If statements with dates before so I don't know where to start and any help will be greatly appreciated.

Thanks,

re: " if"itswithin 4 years since the date in A2By

itsI assume you mean today's date. If so...Take a look at the EDATE function.

EDATE returns the serial number that represents the date that is the indicated number of months before or after a specified date (the start_date).

e.g. For your 5 year (60 months) expiration criteria, this should work.

=IF(EDATE(A2,60)<TODAY(),"Expired","")

Basically what this says is "If I add 60 months to the date in A2 and the result is less than today's date, then the date in A2 must be more than 5 years ago."

The "inverse" of that is to subtract 60 months from today's date to get the same result:

=IF(EDATE(TODAY(),-60)>A2,"Expired","")

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Thanks so much! This was exactly what I needed.

Ask Your Question

Weekly Poll

Do you think Salesforce should have bought Slack?

Discuss in The Lounge

Poll History