conditional formatting error

  • Thread starter Thread starter jmmerrill1
  • Start date Start date
J

jmmerrill1

I am attempting to conditionally format cells as follows:

Cell value is between 1 and 30 = green

Cell value is between 31 and 60 = yellow

Cekk value is greater than 60 = red

I have a broblem in that I have this formula in some cells

=IF(ISBLANK(E8),"",DATEDIF(E8,A1, "D"))

It returns a value of blank but the cell is RED.

I have tried changing the formula to:

=IF(ISBLANK(E8),"0",DATEDIF(E8,A1, "D"))

I then get a value of 0 that is still red.

What am I doing wrong??????? I would like to end up with a blank or
zero with noi shading (white).
 
You could change your formula to return a number--not a string:
=IF(ISBLANK(E8),0,DATEDIF(E8,A1, "D"))
Or
since =datedif() with "D" is the same as subtracting the dates:
=IF(ISBLANK(E8),0,A1-E8)

Or you could change your 3rd conditional formatting rule.

I put the formula in E1 and used this rule:
Formula is:
=AND(ISNUMBER(E1),E1>60)

ps.

I'd use:
=if(e8="","",a1-e8)

It's quicker to type.
 
One way:

Add a check for numeric values in your CF:

CF3: Formula Is =AND(ISNUMBER(E8),E8>60)
Format3: <pattern>/<red>
 
Hi Jim,
You were provided solutions but I don't think that it was pointed out
specifically that a cell with a formula will never test true for ISBLANK
because it has content.

Also a cell with text content of zero length, one with a single space, and
one with two spaces are not equal. You can use TRIM to test.

A value within quotes is a text constant and is not equal to zero.
 
Back
Top