If expired, change color
 

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

If expired, change color
 

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.
--
Gary''s Student


"LMB" wrote:

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

If expired, change color
 

Thanks,

I'll give that a try!

Linda

"Gary''s Student" wrote in message
...
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.
--
Gary''s Student


"LMB" wrote:

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

If expired, change color
 

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.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Gary''s Student" wrote in message
...
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.
--
Gary''s Student


"LMB" wrote:

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