Solved How to If Statements Using Dates in Excel?

June 26, 2015 at 11:01:31
Specs: Windows 7
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,


See More: How to If Statements Using Dates in Excel?

Report •

#1
June 26, 2015 at 12:03:57
✔ Best Answer
re: "if its within 4 years since the date in A2"

By its I 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


Report •

#2
June 29, 2015 at 05:55:35
Thanks so much! This was exactly what I needed.

Report •
Related Solutions


Ask Question