If expired, change color

[LMB]

Hi Guys,

Using Excel 2000. I have a sheet that has dates of annual and bi-annual
certificates. I would like the font color to change if the date on the
sheet is expired. Can I set this up by column since some columns expire in
1 year and others in 2?

The other issue is that if the certificate date is 10/15/05, the actual
expiration date is at the end of the month for both 1 and 2 year renewals.

Thanks,
Linda

 

[Gary''s Student]

I suggest that you explicitly include both the issue and expiration dates in
separate cells. If the issue date is in A1, then the expiration date is :

=DATE(YEAR(A1)+1,MONTH(A1)+1,1)-1 for one year

Then use conditional formatting to compare expiration date to TODAY()


b.t.w use YEAR(A1)+2 for two years.

 

[LMB]

Thanks,

I'll give that a try!

Linda

I suggest that you explicitly include both the issue and expiration dates
in
separate cells. If the issue date is in A1, then the expiration date is :

=DATE(YEAR(A1)+1,MONTH(A1)+1,1)-1 for one year

Then use conditional formatting to compare expiration date to TODAY()


b.t.w use YEAR(A1)+2 for two years.

 

[David McRitchie]

Hi Linda,
For some help with the conditional formatting part see
http://www.mvps.org/dmcritchie/excel/condfmt.htm

The dates leaves quite a bit of interpretation, but if you
wanted to compare a date to today's date you might
use TODAY() in your formula.