| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 . |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional formatting / blank cells | Excel Discussion (Misc queries) | |||
| Conditional formatting / blank cells | Excel Discussion (Misc queries) | |||
| Conditional formatting/non blank cells | Excel Worksheet Functions | |||
| Conditional Formatting Not Blank Cells | Excel Discussion (Misc queries) | |||
| HELP - Conditional Formatting Dates & Blank Cells | Excel Worksheet Functions | |||
Linear Mode
