Conditional Formatting - Dates & Blank Cells
 

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_late1. All my attempts at CF have also
turned the blank (" ") cells red also. I have tried ISBLANK and NOT(ISBLANK).
--
pls

Conditional Formatting - Dates & Blank Cells
 

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)),a11)

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

which can be simplified to:
=a11

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

Regards,
Fred


"Pat" wrote in message
...
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_late1. All my attempts at CF have
also
turned the blank (" ") cells red also. I have tried ISBLANK and
NOT(ISBLANK).
--
pls

Conditional Formatting - Dates & Blank Cells
 

For days_late, thry this in FORMULA IS
=AND(days_late0,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_late1. All my attempts at CF have also
turned the blank (" ") cells red also. I have tried ISBLANK and NOT(ISBLANK).
--
pls

Conditional Formatting - Dates & Blank Cells
 

Yahoo, It is working. Thank you.
--
pls


"Sheeloo" wrote:

For days_late, thry this in FORMULA IS
=AND(days_late0,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_late1. All my attempts at CF have also
turned the blank (" ") cells red also. I have tried ISBLANK and NOT(ISBLANK).
--
pls

Conditional Formatting - Dates & Blank Cells
 

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<"",a11) condition will work, but neither
=and(not(isblank(a1)),a11) nor =a11 would work (or if it does, I'd be
interested to know which issue of Excel you are using).
--
David Biddulph


"Fred Smith" wrote in message
...
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)),a11)

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

which can be simplified to:
=a11

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

Regards,
Fred


"Pat" wrote in message
...
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_late1. All my attempts at CF have
also
turned the blank (" ") cells red also. I have tried ISBLANK and
NOT(ISBLANK).
--
pls

Conditional Formatting - Dates & Blank Cells
 

I copied by worksheet and changed the formula and the CF. I test three
different dates and it seems to work fine.

Thank you
--
pls


"Fred Smith" wrote:

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)),a11)

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

which can be simplified to:
=a11

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

Regards,
Fred


"Pat" wrote in message
...
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_late1. All my attempts at CF have
also
turned the blank (" ") cells red also. I have tried ISBLANK and
NOT(ISBLANK).
--
pls

.

Conditional Formatting - Dates & Blank Cells
 

You're right, David. I was lazy and tested the formulas by simply deleting
the cell contents. My formulas work with a cleared cell, but not with ="".
Thanks for picking up on this. I'm using XL2007.

Regards,
Fred

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
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<"",a11) condition will work, but neither
=and(not(isblank(a1)),a11) nor =a11 would work (or if it does, I'd be
interested to know which issue of Excel you are using).
--
David Biddulph


"Fred Smith" wrote in message
...
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)),a11)

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

which can be simplified to:
=a11

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

Regards,
Fred


"Pat" wrote in message
...
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_late1. All my attempts at CF have
also
turned the blank (" ") cells red also. I have tried ISBLANK and
NOT(ISBLANK).
--
pls