EXPIRED or DATE
 

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


--
I Have forgotten so much of what I once knew.

"A Stranger is a Friend you haven''t met yet."

EXPIRED or DATE
 

See if this is what you're looking for:

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

HTH
Elkar


"Craig A." wrote:

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


--
I Have forgotten so much of what I once knew.

"A Stranger is a Friend you haven''t met yet."

EXPIRED or DATE
 

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(),"Ex pired",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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Craig A." wrote in message
...
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


--
I Have forgotten so much of what I once knew.

"A Stranger is a Friend you haven''t met yet."

EXPIRED or DATE
 

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.
--
I Have forgotten so much of what I once knew.

"A Stranger is a Friend you haven''t met yet."


"Elkar" wrote:

See if this is what you're looking for:

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

HTH
Elkar


"Craig A." wrote:

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


--
I Have forgotten so much of what I once knew.

"A Stranger is a Friend you haven''t met yet."