I have a training matrix which shows the dates when each staff member had
training on a particular piece of equipment. Some of these have expiry dates.
For example three years after the date the certificate was issued for
chainsaw training.
What i would like to do is use some form of conditional formatting so that i
can type the number of years in one column and it would work out three years
from the date the certificate was issued and then turn red when the
certificate expired
For example in column A i would have the date the certificate was issued. In
column B i would have the number of years the certificate ran for. I need a
formula so that the box in column A turns red automatically when its reached
its expiry date.

All help appreciated

If the training date is in cell A1 and the years it is valid is in cell A2
then apply this conditional formatting to cell A1:

Condition - Cell Value - is less than -
=DATE(YEAR(TODAY())-B1,MONTH(TODAY()),DAY(TODAY()))

This formula is based on the current date but could be modified to work with
any date you specify.

Tom

Hi Tom,

Thanks for the reply. I entered the conditional formatting as you said below
and set it so that the cell would change to red once the condition had been
met.
In cell A1 i entered a date of 03-Nov-2006 and in cell A2 i entered 3
(number of years). The cell in A1 changed to red but it shouldn't do until
03-Nov-2009 because the certificate is valid for three years.
I'm not sure why its doing that. Would the date format make any difference?

Kind Regards
Craig

"TomPl" wrote:

If the training date is in cell A1 and the years it is valid is in cell A2
then apply this conditional formatting to cell A1:

Condition - Cell Value - is less than -
=DATE(YEAR(TODAY())-B1,MONTH(TODAY()),DAY(TODAY()))

This formula is based on the current date but could be modified to work with
any date you specify.

Tom

Hi Tom,

Its ok i've got it now. Thanks for your help

"Craig" wrote:

Hi Tom,

Thanks for the reply. I entered the conditional formatting as you said below
and set it so that the cell would change to red once the condition had been
met.
In cell A1 i entered a date of 03-Nov-2006 and in cell A2 i entered 3
(number of years). The cell in A1 changed to red but it shouldn't do until
03-Nov-2009 because the certificate is valid for three years.
I'm not sure why its doing that. Would the date format make any difference?

Kind Regards
Craig

"TomPl" wrote:

If the training date is in cell A1 and the years it is valid is in cell A2
then apply this conditional formatting to cell A1:

Condition - Cell Value - is less than -
=DATE(YEAR(TODAY())-B1,MONTH(TODAY()),DAY(TODAY()))

This formula is based on the current date but could be modified to work with
any date you specify.

Tom