Formula using DATES

[wingwiper]

Hi,

In Excel 2003, I have a Column where I typed in a Date Applied, the Next Column is DATE / EXPIRED.

The Date Applied is Column E3 and the DATE/EXPIRED is F3

Both Columns are Date Columns and I want the date to show in Date Applied and if the date applied is more than 395 days old, I want EXPIRED to print in RED in the column that is labeld DATE/EXPIRED, if it is less than 395 days old, I want the date it is due to Expire.

Currently I am getting the Date it is due to expire to print with no problem, but there are 1000s of names and I want to be able to look at a Glance and see the EXPIRED ones stand out.

I know I am to deal with a Conditional Date Formula, but have been working on it for hours and have not solved it yet.

Microsoft Office 2003 using Excel.

Thanks so much.

Certificates are only good for a Year before they have to be renewed.

Date Amplied ...... Date Expires
Feb 2007............ EXPIRED
Apr 2008 ............April 2009
May 2008 ...........May 2009
Jan 2008............ EXPIRED
Jun 2008 ............Jun 2009


I am currently getting this to print a date, I had a formula of E3+365 and F3 showed the dates as I have indicated. I can not get it to show EXPIRED when the Date is more than 365 days using today's date as reference.
E3+365 should show the regular expiration date but if that date is less than today's date then EXPIRED. Hope I explained it better.

 

[rayburnley]

Not certain how you het the 'expired' to show in red, but I think the formula you want in cell F3 to show either a date or 'EXPIRED' when compared to the date in E3 is :

=IF(TODAY()-E3<365,E3+365,"EXPIRED")

To break this down, it says that if today's date (TODAY()) minus the value in E3 is less than 365, then show as the date in E3 plus 365 ; otherwise show "EXPIRED"

Don't forget that if there's a leap year you'll be a day out !

 

[rayburnley]

Re-reading this, I just noticed that I did that formula for 365 days (thinking you were looking for a 1-year period), not 395 as you wanted, but I'm sure you'll appreciate the slight adjustments you need to make.

 

[rayburnley]

Having had a little 'play' I think that the only way to get 'EXPIRED' to appear in red is to use the Conditional Formatting option on that column, so that if the contents of a cell are "EXPIRED" it will show up in red - or indeed whatever you want, different font, font size, bold, etc.

Other more experienced persons may be able to suggest an alternative however.

Hope this helps and doesn't confuse !!

Ray

 

[wingwiper]

Thanks Ray

I was only using 395 days so if they applied in the begining of the month, it would expire at the end of the month, but as I think about it, that won't apply.
I am using your formula and am working on the Red part and thanks so much for your reply. It seems to work and I will tweak from there.

Wingwiper