conditional formatting error

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jmmerrill1@verizon.net - 18 Aug 2007 16:19 GMT

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).

Reply to this Message

Dave Peterson - 18 Aug 2007 16:34 GMT

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.

> I am attempting to conditionally format cells as follows:
>
[quoted text clipped - 18 lines]
> What am I doing wrong??????? I would like to end up with a blank or
> zero with noi shading (white).

Signature

Dave Peterson

Reply to this Message

JE McGimpsey - 18 Aug 2007 16:38 GMT

One way:

Add a check for numeric values in your CF:

CF3: Formula Is =AND(ISNUMBER(E8),E8>60)
Format3: <pattern>/<red>

> I am attempting to conditionally format cells as follows:
>
[quoted text clipped - 18 lines]
> What am I doing wrong??????? I would like to end up with a blank or
> zero with noi shading (white).

Reply to this Message

David McRitchie - 20 Aug 2007 14:11 GMT

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.

--
HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm

>I am attempting to conditionally format cells as follows:
>
[quoted text clipped - 18 lines]
> What am I doing wrong??????? I would like to end up with a blank or
> zero with noi shading (white).

Reply to this Message

Rate this thread: