Conditional Formatting 4th Condition

[Dax Arroway]

Help with conditional formatting please. I'm designing a "tickler" sheet to
get cells to color by comparing itself to the date of another cell. There's
4 different conditions. I'm using Excel03 and there's only 3 available
conditions. Here's what I need to do.

Cell C2 has a Date.
Cell G2 needs to stay blank if C2=""
Cell G2 needs to turn blue when C2 is beyond 20 days past G2
Cell G2 needs to turn red when C2 is beyond 30 days past G2 --AND--
Cell G2 needs no format if any text is present in G2

I've figured:
Condition 1 in G2 is =$G2="*" No format (I'm using * as "any text" but I
don't think that's right)
Condition 2 in G2 is =$C2+30<TODAY() Color Red
Condition 3 in G2 is =$C2+20<TODAY() Color Blue
And I need one more condition, a Condition 0, which would be G2 is =$C2=""
no format.

If there's another way, please help.
Maybe Condition 1 could be: If C2 is blank OR if there's text in G2, don't
format. But I don't know how to write that in code-speak. Am I even on the
right track??

Thanks in advance!
--Dax

 

[Dax Arroway]

I said, "Cell C2 needs to stay blank if C2=""" I meant that if there's no
data in C2, not to format G2 at all.

 

[Gord Dibben]

Why would G2 need to contain anything when all the conditions are based on
C2?

You are comparing the date in C2 to TODAY(), not to a value in G2

Select G2 and CF>Formula is:

Condition1 =$C2+30<TODAY()

Condition2 =$C2+20<TODAY()


Gord Dibben MS Excel MVP

 

[Dax Arroway]

Because it's a "tickler" which needs to have the funcitonality of being
"turned off"

So, in English, a service needs to be done and then a follow up service 30
days later. Service Date goes in C2. G2 is the indicator which I'm putting
in the conditional formatting. So, if nothing is in C2, G2 should remain
unformatted. 20 days later G2 needs to fill blue to "tickle" that the next
service is coming due for that record. At 30 days it needs to fill red to
indicate Due or Past Due. Then it needs to be turned off when the services
is actually done, which I'm doing by having people fill in a date into the
colored G2 cell. In other words, I need the conditional formatting to "turn
off" if any text is present in G2.

The way you indicated turns the cell red if no data is present in C2 which
is not the desired function I'm looking for.

Any other help???