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.

(E-Mail Removed) wrote:
>
> 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).

--

Dave Peterson

One way:

Add a check for numeric values in your CF:

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

In article <(E-Mail Removed)>,
(E-Mail Removed) wrote:

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

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

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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).
>