Conditional Formatting in Excel 2003

[Jade Summers]

Good day everyone,

I have an excel sheet to keep track of who has taken a course, when the
took it, which automatically calculates ten years from then for th
expirtation date.

My issue is even the blank cells for people who did not take the cours
(H2 Expiry date) get affected by my conditional formatting and tur
green which is only supposed to be for dates 6 months past today an
over.

I want to make the blank cells in H to stay white, which my conditiona
formatting is still effective for the other information.

Here are my formulas:

H2 to add 10 years to when they took the course
=IF(G2="","",DATE(YEAR(G2)+10,MONTH(G2),DAY(G2)))

Conditional formating
Green
=$H10>TODAY()+181

Red
=AND($H10>0,$H10<TODAY())

Yellow
=TODAY()-$H10<180

I know I am missing something obvious, and I thought I had already fixe
it but I can't seem to figure it out.

Thank you for your help

 

[Claus Busch]

Hi Jade,

Am Fri, 3 Aug 2012 16:06:00 +0000 schrieb Jade Summers:

Conditional formating
Green
=$H10>TODAY()+181

try:
=AND(LEN(H2)>0,H2>TODAY()+181)


Regards
Claus Busch

 

[Jade Summers]

Hi Jade,

Am Fri, 3 Aug 2012 16:06:00 +0000 schrieb Jade Summers:
-

try:
=AND(LEN(H2)>0,H2>TODAY()+181)


Regards
Claus Busch

As per usual that worked wonderfully! Thank you