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 10:13 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 26 Feb 2010 10:55 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 26 Feb 2010 11:07 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 26 Feb 2010 11:33 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 26 Feb 2010 11:33
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 > |