From: Pat on
I have read & tried several CF solutions, but they are not working for me.
I am working with Today() date, Due_Date, & days_late.

I have a formula in the days_late column
=IF(OR(TODAY()<Due_Date,J9="COMPLETED")," ",TODAY()-Due_Date). This formula
gives me days_late or leaves that column blank (" ").

I want 5 cells to turn red if days_late>1. All my attempts at CF have also
turned the blank (" ") cells red also. I have tried ISBLANK and NOT(ISBLANK).
--
pls
From: Fred Smith on
Your problem is that " " is not blank. I know it looks blank to you, but not
to Excel. You need to use null ("") to have ISBLANK be true. So change your
formula to :
=IF(OR(TODAY()<Due_Date,J9="COMPLETED"),"",TODAY()-Due_Date)

Your conational formula would be:
=and(not(isblank(a1)),a1>1)

which can be simplified to:
=and(a1<>"",a1>1)

which can be simplified to:
=a1>1

In the future, you can avoid these annoying problems by always using null
("") rather than space (" ") to signify an empty cell.

Regards,
Fred


"Pat" <Pat(a)discussions.microsoft.com> wrote in message
news:17705B52-E906-4F29-8865-8CF0A6177439(a)microsoft.com...
>I have read & tried several CF solutions, but they are not working for me.
> I am working with Today() date, Due_Date, & days_late.
>
> I have a formula in the days_late column
> ). This formula
> gives me days_late or leaves that column blank (" ").
>
> I want 5 cells to turn red if days_late>1. All my attempts at CF have
> also
> turned the blank (" ") cells red also. I have tried ISBLANK and
> NOT(ISBLANK).
> --
> pls

From: Sheeloo on
For days_late, thry this in FORMULA IS
=AND(days_late>0,days_late<>" ")

"Pat" wrote:

> I have read & tried several CF solutions, but they are not working for me.
> I am working with Today() date, Due_Date, & days_late.
>
> I have a formula in the days_late column
> =IF(OR(TODAY()<Due_Date,J9="COMPLETED")," ",TODAY()-Due_Date). This formula
> gives me days_late or leaves that column blank (" ").
>
> I want 5 cells to turn red if days_late>1. All my attempts at CF have also
> turned the blank (" ") cells red also. I have tried ISBLANK and NOT(ISBLANK).
> --
> pls
From: Pat on
Yahoo, It is working. Thank you.
--
pls


"Sheeloo" wrote:

> For days_late, thry this in FORMULA IS
> =AND(days_late>0,days_late<>" ")
>
> "Pat" wrote:
>
> > I have read & tried several CF solutions, but they are not working for me.
> > I am working with Today() date, Due_Date, & days_late.
> >
> > I have a formula in the days_late column
> > =IF(OR(TODAY()<Due_Date,J9="COMPLETED")," ",TODAY()-Due_Date). This formula
> > gives me days_late or leaves that column blank (" ").
> >
> > I want 5 cells to turn red if days_late>1. All my attempts at CF have also
> > turned the blank (" ") cells red also. I have tried ISBLANK and NOT(ISBLANK).
> > --
> > pls
From: "David Biddulph" groups [at] on
A minor correction, Fred. Even "" will not satisfy the ISBLANK condition;
the cell needs to be genuinely empty for that.
But you are right to recommend "". With "" in the formula, your
=and(a1<>"",a1>1) condition will work, but neither
=and(not(isblank(a1)),a1>1) nor =a1>1 would work (or if it does, I'd be
interested to know which issue of Excel you are using).
--
David Biddulph


"Fred Smith" <fsmith11(a)yahooo.com> wrote in message
news:#qsomwvtKHA.4568(a)TK2MSFTNGP05.phx.gbl...
> Your problem is that " " is not blank. I know it looks blank to you, but
> not to Excel. You need to use null ("") to have ISBLANK be true. So change
> your formula to :
> =IF(OR(TODAY()<Due_Date,J9="COMPLETED"),"",TODAY()-Due_Date)
>
> Your conational formula would be:
> =and(not(isblank(a1)),a1>1)
>
> which can be simplified to:
> =and(a1<>"",a1>1)
>
> which can be simplified to:
> =a1>1
>
> In the future, you can avoid these annoying problems by always using null
> ("") rather than space (" ") to signify an empty cell.
>
> Regards,
> Fred
>
>
> "Pat" <Pat(a)discussions.microsoft.com> wrote in message
> news:17705B52-E906-4F29-8865-8CF0A6177439(a)microsoft.com...
>>I have read & tried several CF solutions, but they are not working for me.
>> I am working with Today() date, Due_Date, & days_late.
>>
>> I have a formula in the days_late column
>> ). This formula
>> gives me days_late or leaves that column blank (" ").
>>
>> I want 5 cells to turn red if days_late>1. All my attempts at CF have
>> also
>> turned the blank (" ") cells red also. I have tried ISBLANK and
>> NOT(ISBLANK).
>> --
>> pls
>