Conditional Formatting - Dates & Blank Cells
in [General]
|
Prev: How do I reference a cell to automatically update in a header
Next: Conditional formatting causing auto complete to deactivate?
From: Pat on 26 Feb 2010 11:44 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)),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: Fred Smith on 26 Feb 2010 12:23
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 news:pM6dnRdWOZRHahrWnZ2dnUVZ8uSdnZ2d(a)bt.com... >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 >> > |