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_late>1.  All my attempts at CF have also 
turned the blank (" ") cells red also.  I have tried ISBLANK and NOT(ISBLANK).
-- 
pls
0
Utf
2/26/2010 3:13:01 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
747 Views

Similar Articles

[PageSpeed] 29

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@discussions.microsoft.com> wrote in message 
news:17705B52-E906-4F29-8865-8CF0A6177439@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 

0
Fred
2/26/2010 3:55:37 PM
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
0
Utf
2/26/2010 4:07:01 PM
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
0
Utf
2/26/2010 4:33:02 PM
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@yahooo.com> wrote in message 
news:#qsomwvtKHA.4568@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@discussions.microsoft.com> wrote in message 
> news:17705B52-E906-4F29-8865-8CF0A6177439@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
> 

0
David
2/26/2010 4:33:28 PM
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@discussions.microsoft.com> wrote in message 
> news:17705B52-E906-4F29-8865-8CF0A6177439@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 
> 
> .
> 
0
Utf
2/26/2010 4:44:01 PM
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@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@yahooo.com> wrote in message 
> news:#qsomwvtKHA.4568@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@discussions.microsoft.com> wrote in message 
>> news:17705B52-E906-4F29-8865-8CF0A6177439@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
>>
> 

0
Fred
2/26/2010 5:23:36 PM
Reply:

Similar Artilces:

Gantt Chart in Excel. Do I need conditional formatting?
Hello all. I have created a Gantt chart that shows approx. 15 tasks in each project. I am using this to show percentage of completion for each task. It is working just fine and looks great but I need it to insert a date when a task is at 100% completion to show the client the date that particular task was completed. How would I do this without ruining my chart I already have working fine? I know this is probably easy to do but I cannot seem to keep the rest of the chart intact. Is conditional formatting the answer? Thanks! You could use Rob Bovey's XY Chart Labeler to do this: h...

multiple conditional formats
How can I set up a worksheet for more than 3 conditional formats You can't. You would have to use a VBA macro to apply the formatting, which would be static and not change with changes in the cell values -- you'd have to run the macro again. On Mon, 27 Sep 2004 18:57:03 -0700, "RC" <RC@discussions.microsoft.com> wrote: >How can I set up a worksheet for more than 3 conditional formats On Mon, 27 Sep 2004 18:57:03 -0700, "RC" <RC@discussions.microsoft.com> wrote: >How can I set up a worksheet for more than 3 conditional formats You can use ...

Percentage format lost
I have a form with an unbound text box whose row source type is this select query: SELECT CheckFinalPercentages.AllocType, CheckFinalPercentages.[Master-Sub], CheckFinalPercentages.SumOfPERCOUT AS Pct FROM CheckFinalPercentages ORDER BY CheckFinalPercentages.AllocType DESC , CheckFinalPercentages.[Master-Sub]; The query runs fine and returns the SumOfPERCOUT in percent format when I run the query...it used to return it in percent format also on the form, but I must have done something that made me lose it. Now, each time I open the form, the third column is returned in number format (1...

remember path when linking many GIFs to cells?
When I link a number of GIF images residing in a different directory to Excel 2002 cells, the program does not remember the last directory it looked in when a new link is entered. Is there a way to set up a default path for this boring operation? Thanks, z.entropic ...

How do I change the format of how my active cell in Excel is view.
I sometimes have trouble locating which cell is active in my Excel worksheet. I would like to be able to change it to something other than just a heavy black border. Here is one way that highlights the row and column of the activecell. Private Sub WorkSheet_SelectionChange(ByVal Target As Range) Cells.FormatConditions.Delete With Target.EntireRow .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE" .FormatConditions(1).Interior.ColorIndex = 35 End With With Target.EntireColumn .FormatConditions.Delete .FormatConditions.Add T...

format changes on pivot table although preserve formatting is che.
Post the question the body of the message, subject lines get truncated -- Regards, Peo Sjoblom "Aannd" <Aannd@discussions.microsoft.com> wrote in message news:282F34F1-42CA-4F0A-8A62-A71C64E30741@microsoft.com... > ...

Determine if another cell is hidden in a Formula
Hi! I would like to display a special message in a cell when another cell is hidden. Is it possible? Something like (in cell A2): =If(IsHidden(A1), A1, "") I guess I could do it with a user function... but if I use that in a lot of cells (hundreds) it might significantly slow down the refresh speed... Any thoughts ? J Whales ...

Display Sheetname in a cell
I'd like to display a worksheet name "with the body" instead of "in the margins" (ie, using Header / Footer definitions). Is there a built-in mechanism for picking up the worksheet name in a formula? I tried putting Function GetSheetName() As String GetSheetName = ActiveSheet.Name End Function in a code module and " = GetSheetName() " in a cell; but quickly discovered that <F9> doesn't cause the user defined function to re-evaluate. Suggestions? -- Clif Try this: =MID(CELL("filename",A1),FIND("]",CELL("fil...

Hyperlink directly to a cell in a worksheet
Hi there, I'd like to hyperlink directly to a cell in a spreadsheet (spreadsheet opens to a specific cell selected). The link I'm using opens the spreadsheet, but it goes to the same cell everytime. Is there a way to do this? Thanks in advance for any help. Here's what the link I'm using looks like: www.myserver.com/Payroll_Risks_&_Controls.xls#='Risk Scores 1, 2, or 3'!$A$27 Risk Scores 1, 2, or 3 is the name of the worksheet and A27 is the cell I want to go to. ...

Need help removing characters in multiple cells
I have an excel 2007 sheet that has over 300 cells in a column that have a name of a store plus a number between paranethesis. Is there a one step way I can remove the paranethesis? Here is an example: K-Mart (ABC#73846365) and I want to have just the following K-Mart Each cell has a different store name and set of numbers within the paranethesis. Thanks Select the range to fix. Edit|replace what: _(* (spacebar, open paren, asterisk) with: (leave blank) replace all It looked like you'd want to remove the space character after the t in k-mart. "<----- Mardm...

drag and drop problem with cell references
i have a cell with a formula in one worksheet that references another cell. the formula looks like this: =IF((ISBLANK('Draft'!$D25)),"",'Draft'!$D25) where 'draft' is another worksheet in my workbook. the formula works fine, it looks empty if there is nothing in the referenced cell('Draft'!$D25), otherwise it mirrors the content of that cell. If i type something in the referenced cell('Draft'!$D25), it still works great. But if i drag something to the referenced cell than the formula gets screwed up and replaces the $D25, with #ref. I am gue...

Conditional formatting of text
Greetings, all - I am creating a simple spreadsheet template to be distributed to users for data entry purposes. The first column contains the names of the desired data elements. The second column contains the definition for each data element. The definitions are formatted in red text. I intend for the user to remove the definition and replace it with the proper information for each element. For example: Data Element Definition 1.0 LastName The last name of the person to whom questions about the sample should be directed. The user would replace ...

CONVERT TIME & TRIM DATE
I need help converting time to an AM/PM time format our database displays time in 4 digits : 0006, 0737, 1217, 2149. I am finding that 0006 is 12:06 AM and 1217 is 12:17 PM, etc. The date displays: 2007-03-09 00:00:00.000, how do I have it display the date as 03-09-2007? Thanks. Specify the display format of the control or field: mm-dd-yyyy RENEE705 wrote: >I need help converting time to an AM/PM time format >our database displays time in 4 digits : 0006, 0737, 1217, 2149. I am >finding that 0006 is 12:06 AM and 1217 is 12:17 PM, etc. >The date displays: 2007-03-09 00:00:00.0...

Linked Cell Property In Activex controls
Can someone point me to an example showing how this property can be used, linking, as an example, an option button to a specific cell? Say if I wanted "1" to appear in cell B2 of the worksheet if the option button is clicked KG, The option button puts TRUE or FALSE into its Linked Cell. You can get 1 or 0 out of that by referring to it with double negation operators. = --A1 -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "KG" <KG@discussions.microsoft.com> wrote in message news:924E6B40-6040-4016-B75B...

Signature format changes in replies with Outlook 2007
Hi We are using Outlook 2007 and are finding that our signatures will sometimes change font and spacing in reply messages. Is there a way to fix this? Thanks ...

help with range lookup and date criteria
Hello, I have a list of doctors (column A) that are each on-cal through a number of days. My argument is: if the date value of B1 an C1 is within the current date, then repeat the value of A1 -- James Spaldin ----------------------------------------------------------------------- James Spalding's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2567 View this thread: http://www.excelforum.com/showthread.php?threadid=39088 hi, James ! > ... a list of doctors (column A) that are each on-call through a number of days. > ... argument is: if the date value ...

Publisher File formats
I need to send file in a tif or jpeg format but when I save the file I still get the pub extension after the .tif' Can I not save in anything but pub? Thanks Ray, It would help us to answer your question if you told us which version of Publisher you are using. -- JoAnn Two things are aesthetically perfect in the world - the clock and the cat. --Emile-August Chartier "Ray" <anonymous@discussions.microsoft.com> wrote in message news:006101c3dc62$7649ce40$a501280a@phx.gbl... > I need to send file in a tif or jpeg format but when I > save the f...

CFtpFileFind return null creation date
Hi All I am trying to list the URL and timestamp of some files on a server in a list box in a simple dialog using FTP. The code is as follows: void CFTPTestDlg::OnTest() { CListBox* pbox = (CListBox*)GetDlgItem(IDC_LB_FILES); pbox->ResetContent(); // Create session object to initialise libs CInternetSession sess(_T("My Test")); // Declare a FTPConnection CFtpConnection* pftpConn = NULL; try{ pftpConn = sess.GetFtpConnection("ftp.,mytestsite.co.uk", "mylogon","mypassword"); if (!pftpConn->SetCurrentDirectory("/Testdir/T...

extract year from Date Value
Good morning, Could someone help me extract the year portion from a date value such as this 11/20/2009? Thanks in advance, Mike With the Date in A1, place =YEAR(A1) in B1 Takeadoe wrote: > Good morning, > > Could someone help me extract the year portion from a date value such > as this 11/20/2009? > > Thanks in advance, > > Mike ...

Removing Blank Cells #3
I would like to know if there is a way to remove the blank columns and rows from a worksheet. There really is no need for this many blank rows in the workpaper I am working on and I assume it contributes to the overall size of the spreadsheet. Thanks in advance. Hi you can't remove them (you can hide them if you like9. Also they do not add up to the size of your worksheet 8at least as long as your used range is correct) -- Regards Frank Kabel Frankfurt, Germany Jeff Mortenson wrote: > I would like to know if there is a way to remove the blank > columns and rows from a works...

how do I add a 'web address' to a cell in excel with a hyperlink?
how do I add a 'web address' to a cell in excel with a hyperlink? I am a new student to computers, and part of a test is to insert a hyperlink to a cell in excel linking to a web address. I have searched computer for any 'Hyperlink' connection words, but I think it not on here, Is that possible, although I have top of range computer. Anyone with a solution? I be ever so greatful. in xl2002 ctrl+k>in the link to select existing web page>either type in (at the bottom) or point to the url on your browser>enter -- Don Guillett SalesAid Software dguillett1@austi...

Conditional Format #23
I want to format the background of cell to a10 red if a10 is currently empty and the length of the string in a5 is 1. Can somone offer the correct syntax for the conditional format of a10 as everything I try fails. Thanks, Fred You need a condition of Formula is and a formula of =AND(A10="",LEN(A5)=1) -- HTH RP (remove nothere from the email address if mailing direct) "Fred" <Fred@discussions.microsoft.com> wrote in message news:2EBDDC73-FED7-451F-BCF3-A0E819888027@microsoft.com... > I want to format the background of cell to a10 red if a10 is currently...

overlaping dates in two files
MS Money 2001 deluxe on Windows XP Pro I archived my 2004 accounts and never balanced them. I decided i needed to and now have two files, both .mny files with 2003Archive.mny is from 1/2002 to 12/2004 Active2005.mny is from 1/2004 to present. I'd like to combine them into one file so I can balance the accounts (three main accounts) because my statements are not ending nicely on the end of the month. Please recommend the best method so I can balance the accounts. I think I need to conbine the files or somehow get the months that are now balanced in the 2003Archive.mny file to give...

Suppres Zero or empty Cell value in a line graph
Hi I'm using Office 2007. I have two charts using data from the same sheet. The second graph is a copy of the first. In the first graph, the empty and zero value cells are not displayed. In the second graph, the zero value cells is displayed (draged to zero) although the option "connect points with line" is checked. The strange thing: if I change the Y-data to another part of the sheet, it is no longer dragged to zero. Even if the cell is empty it's still dragged to zero A formula that returns "" is not an empty cell, it's a formula (or it's a small ...

Help with Date/Time Function
I am working with date/time functions trying to figure out how many hours were spent doing a job. I have a start time and a finish time. But I need to subtract out any time between five pm and six am and weekends from the times. These would be times that no one was actually working on the job. For example I have 4/5/2005 18:23 4/6/2005 17:04 Could someone give me a clue as to how this might be done? Thanks, Steve Monczka smonczka@hotmail.com It's not a trivial thing, here's an example http://www.cpearson.com/excel/DateTimeWS.htm or here http://tinyurl.com/cbphn -- R...