Need to highlight a cell based on a one year expiration

[sdarnell]

I have a very basic spreadsheet with names and dates of last training. The training is due every year so if they took it on 1/1/2013 it would be due again on 1/1/2014.

In A3 to A7, I have names. In B3 to B7 I have their last training date. I want the date cells to highlight yellow when they are within 60 days of expiring and then highlight red when they are more than 1 year old.

I have tried several different things and cannot make it work. I am very much a novice so as simple as possible would be great...

TIA
Stephen

[etaf]

use a formula and two rules

=b3<=date(year(today())-1,month(today()),day(today()))
format red

next rule
=b3<=date(year(today()),month(today()),day(today())-305)
format yellow
that will test 60 days before year end

make sure you order - red first , then yellow and use stop if true

and apply to the range
$B$3:$B$7

if you post your spreadsheet we can add for you

[sdarnell]

Worked perfectly... Thx...

How about something to apply to a cell (with the same restrictions as above) but the cell is blank? With the formatting I applied, the cell turns red if it is empty with no data in it.

[etaf]

you could add a new rule and put first

=B3=""
no format

OR add to the rules you have

=AND(B3<=date(year(today())-1,month(today()),day(today())), B3<>"")
format red

next rule
=AND (B3<=date(year(today()),month(today()),day(today())-305), B3<>"")
format yellow

if my assistance has helped, and only if you wish to , there is a reputation icon * on the left hand side - you can add to my reputation here

[sdarnell]

Absolutely perfect.. Thank you so much... Reputation added

I sincerely appreciate it...

[etaf]

your welcome, thanks for the rep