Formula using DATES

W

wingwiper

Joined
Mar 4, 2009
Messages
2
Reaction score
0
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.
 
Last edited:
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 !
 
Last edited:
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.
 
Last edited:
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
 
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
 
Back
Top