Conditional Formatting - Dates & Blank Cells

  • Thread starter Thread starter Pat
  • Start date Start date
P

Pat

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).
 
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
 
I copied by worksheet and changed the formula and the CF. I test three
different dates and it seems to work fine.

Thank you
 
Back
Top