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

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
>
>
>

Thanks,

I'll give that a try!

Linda

"Gary''s Student" <[email protected]> wrote in message
news:[email protected]...
>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
>>
>>
>>

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" <[email protected]> wrote in message
news:[email protected]...
> 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
> >
> >
> >