EXPIRED or DATE

[Craig A.]

Hi!

I am using Excel 2003. In cell A1 I have the Current Date.

In Column E3 ( DATE CERTIFIED) I have a date entered that shows when a
person was Certified.
Column E4 (DATE EXPIRES) I want to show the date that the person was
certified in a Mont Year Format. (No Problem, I succeeded in that part of it)
and if has been over a year using A1 or Today's Date, I want the word EXPIRED
to appear where the date once was.

I have played with many veriations of the formula and have gone to online
help etc and have had no success. I really want to use a Spreadsheet and not
a Database for other reasons,. Can what I am asking be done?

Thanks for the help

DATE CERTIFIED DATE EXPIRES

Mar 07 EXPIRED
Apr 08 Apr 09
Jun 08 Jun 09
Mar 09 Mar 10
Dec 08 Dec 09
Aug 07 EXPIRED

Thank you

 

[Elkar]

See if this is what you're looking for:

=IF(DATE(YEAR(E3)+1,MONTH(E3),DAY(E3))>TODAY(),DATE(YEAR(E3)+1,MONTH(E3),DAY(E3)),"Expired")

HTH
Elkar

 

[Bernard Liengme]

Your references to E3 and E4 do not seem to jibe with the table your show.
So I will work with the table
Let's say Mar 07 be in cell E3 and let it have been entered as 1/3/2007 (or
3/1/2007 if you are in USA) and formatted with mmm yy
In F3 to compute the expiry date we could use
=DATE(YEAR(E3)+1,MONTH(E3),DAY(E3))
But we want this to show text "Expired" if that date is prior to today's
date, so we use
=IF(DATE(YEAR(E4)+1,MONTH(E4),DAY(E4))<TODAY(),"Expired",DATE(YEAR(E4)+1,MONTH(E4),DAY(E4)))
Remember to still format this with mmm yy.

If you have many rows you might wish to experiment with conditional
formatting to make the expired data stand out in colour
best wishes

 

[Craig A.]

EXCELLENT!!

YES! that seems to be doing the job, I want.

Thanks so much.

I wasn't even close in my formula and I had been working on it for hours.
Thanks again.