Conditional formatting with dates formula problem.

Hello.
I appreciate help on this topic.  I'm very new to excel's conditional
formatting capabilities and I need help on the following:

I have a worksheet where I am using columns A and B to be fashioned
into a type of "reverse" library checkout card; I want to flag when 120
days have passed since an item has been checked out.  All cells are
blank with the exception of the formatting applied to cells in column
A.  For example, Condition 1 on cell A1 has the formula:

=IF(ISBLANK(B1),(A1-TODAY())<120)

**I've also tried =IF(ISBLANK($B1),($A1-TODAY())<120)

My objective is to turn any cell in column A green with white text when
any date entered is over 120 days overdue; there are no fixed dates
already entered.  Dates are entered in on column A as the item is
checked in.

My formula works to some extent.  The problem I'm experiencing is that
the column A cells turn green before ANY date is entered.  The
condition is tested before the date is entered.  When the date is
entered, the text turns white, as expected.  Once I type the check-out
date in cell B1, it turns cell A1 back to normal text/background; that
part works fine.

I've searched this forum for clues.  A couple of postings are close to
what I want and I've tested.  But they are working with values already
in the cells.

0
3/5/2005 6:22:16 PM
excel.misc 78881 articles. 5 followers. Follow

8 Replies
212 Views

Similar Articles

[PageSpeed] 58

Sorry, I hit the post button too soon....

Thanks for your help!

Russ

0
3/5/2005 6:27:57 PM
Hi!

Your explanation is not very clear!

>All cells are blank

>there are no fixed dates already entered. Dates are 
>entered in on column A as the item is checked in.

>Once I type the check-out date in cell B1

Am I missing something here? Check out dates are in column 
B and returned dates are in column A?

Conditional Formatting
Formula is: =AND(B2<>"",TODAY()>=B2+120,A2="")

If that's not what you want post back with an easier to 
understand explanation! <g>

Biff

>-----Original Message-----
>Hello.
>I appreciate help on this topic.  I'm very new to excel's 
conditional
>formatting capabilities and I need help on the following:
>
>I have a worksheet where I am using columns A and B to be 
fashioned
>into a type of "reverse" library checkout card; I want to 
flag when 120
>days have passed since an item has been checked out.  All 
cells are
>blank with the exception of the formatting applied to 
cells in column
>A.  For example, Condition 1 on cell A1 has the formula:
>
>=IF(ISBLANK(B1),(A1-TODAY())<120)
>
>**I've also tried =IF(ISBLANK($B1),($A1-TODAY())<120)
>
>My objective is to turn any cell in column A green with 
white text when
>any date entered is over 120 days overdue; there are no 
fixed dates
>already entered.  Dates are entered in on column A as the 
item is
>checked in.
>
>My formula works to some extent.  The problem I'm 
experiencing is that
>the column A cells turn green before ANY date is 
entered.  The
>condition is tested before the date is entered.  When the 
date is
>entered, the text turns white, as expected.  Once I type 
the check-out
>date in cell B1, it turns cell A1 back to normal 
text/background; that
>part works fine.
>
>I've searched this forum for clues.  A couple of postings 
are close to
>what I want and I've tested.  But they are working with 
values already
>in the cells.
>
>.
>
0
biffinpitt (3172)
3/5/2005 8:18:05 PM
Hi Biff,
Thanks for replying so quickly!  I know.. the more I tried to explain,
the weird-er it got...Let give it another shot:

I have columns A and B.  Both have blank cells A1 and B1 with no
formatting.  Cell A1 currently has conditional formatting of

=IF(ISBLANK(B1),(A1-TODAY())<120)

What I'm shooting for is when I enter a date of 9/1/04 into the blank
cell A1, I want the condition to trigger because 1) it is over 120 days
ago and 2) B1 is blank.  Cell A1 will then format into a green
background with the date in white text.  Later, when I enter 9/14/04
into B1, the condition is no longer true, so A1 goes back to it's no
format look (how/why it is doing that, I don't know).

What I'm experiencing is when I apply the conditional formatting to
cell A1, as soon as I hit OK on the conditional formatting dialog box,
the cell background turns green without me entering a date.  I'm trying
to figure out why the condition is true without me entering a date for
it to evaluate against.  The only thing I can figure is that my ISBLANK
function is returning true and turning the cell green without me
entering a date.

Thanks for reading my rambling...
Russ

0
3/5/2005 9:01:21 PM
This seemed to work for me:

=AND(B1="",TODAY()-A1>120)
-- 

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

<russell.estes@gmail.com> wrote in message
news:1110056481.759981.94610@o13g2000cwo.googlegroups.com...
Hi Biff,
Thanks for replying so quickly!  I know.. the more I tried to explain,
the weird-er it got...Let give it another shot:

I have columns A and B.  Both have blank cells A1 and B1 with no
formatting.  Cell A1 currently has conditional formatting of

=IF(ISBLANK(B1),(A1-TODAY())<120)

What I'm shooting for is when I enter a date of 9/1/04 into the blank
cell A1, I want the condition to trigger because 1) it is over 120 days
ago and 2) B1 is blank.  Cell A1 will then format into a green
background with the date in white text.  Later, when I enter 9/14/04
into B1, the condition is no longer true, so A1 goes back to it's no
format look (how/why it is doing that, I don't know).

What I'm experiencing is when I apply the conditional formatting to
cell A1, as soon as I hit OK on the conditional formatting dialog box,
the cell background turns green without me entering a date.  I'm trying
to figure out why the condition is true without me entering a date for
it to evaluate against.  The only thing I can figure is that my ISBLANK
function is returning true and turning the cell green without me
entering a date.

Thanks for reading my rambling...
Russ


0
ragdyer1 (4060)
3/5/2005 9:20:03 PM
Hi RD,
Grrrr, I'm not sure why the cell keeps changing color even though I
haven't typed anything into cell.  I copied your formula into the
conditional formatting field and chose my format.  I hit OK and the
cell turned green (the format for my cell background).

I tried it on a brand new workbook and I'm using Excel 2002, SP3.
Maybe MS site will have info on why the condition is firing when only
one half of the formula is true.

Thanks again for your help.
Russ

0
3/5/2005 9:43:36 PM
Let's start from the beginning.

I selected *only* cell A1 when I entered this CF, and it  (A1) worked as
advertised.

Did you only have A1 selected when you entered this CF?
-- 

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------


<russell.estes@gmail.com> wrote in message
news:1110059016.806374.273580@z14g2000cwz.googlegroups.com...
Hi RD,
Grrrr, I'm not sure why the cell keeps changing color even though I
haven't typed anything into cell.  I copied your formula into the
conditional formatting field and chose my format.  I hit OK and the
cell turned green (the format for my cell background).

I tried it on a brand new workbook and I'm using Excel 2002, SP3.
Maybe MS site will have info on why the condition is firing when only
one half of the formula is true.

Thanks again for your help.
Russ


0
ragdyer1 (4060)
3/5/2005 10:24:17 PM
Thanks RD,
I am only selecting cell A1.  I am seeing a problem with the workbook
i'm working with.  I tried your formula on another system running the
same version of excel.  When I entered the CF, the cell didn't turn
green prior to me entering a date.  But it didn't turn green after I
entered a date either.

So I deleted my workbook and started brand new.  I can now replicate
the behavior I just wrote about.

I wanted to ask you what Number format are you using?  Maybe I'm using
the wrong format, if that makes a difference...

Russ

0
3/5/2005 11:27:34 PM
Thanks to everyone...I don't know why I didn't try this first.  My
workbook was messed up in the first place, how I don't why.  Probably
due to my tinkering.  Even after I deleted all CF and manually cleared
the formatting, the workbook wanted to keep it for some reason...but
deleting it and starting with an absolutely brand new workbook seemed
to have worked.

I also performed the following which I don't know if it fixed my
problem or not.  All I know is that this is working now.

1)  Format Cells > Number > Date > selected the very first option:
*3/14/2001.  I was using 3/14/01 first.

2)  After entering the CF, I went back in CF to verify my entry.  I
removed the quotes Excel put in for me.

All works now.

Thanks again...
Russ

0
3/5/2005 11:47:48 PM
Reply:

Similar Artilces:

Creating a new field based on conditions
I have a database that tracks insurance information for our various vendors. Each insurance type has 2 fields - a requirement field (yes/no), and an effective field (some show an expiration date, some are yes/no). I have created a query that will return only the records for which insurance is required but is expired/missing. My problem is that I want to create a new field that is calculated based on the values in the other two fields in order to make the resulting report more user-friendly. For example, if GLRequired is True and GLExpiration is <Now(), I want the new field to say...

parsing a date and time field #2
I am having trouble parsing the date and time in a field. I download data from a data base and the date and time come together in one field. I want to seperate the two. The date and time comes across as the following: "2/1/2009 14:37" in the cell. When I parse it, it seperates into three columns as follows: "2/1/2009", 2:37 AM", and "PM" I can see what is going on but I would like to get two columns with one as the date and the other as the correct time. are they any ideas on how to address this? Try using the TimeValue and DateValue functions. First format ...

Save formatted text from RichEdit control to rtf-file
Hi , How can I save the text from Rich edit control (2.0) to *.rtf , *.txt , *.doc I tried to get the buffer and putting the buffer to file, then saving the file but the text in the file is something different. Please let me know what to do? Here is the Code I ma using: mFile.Seek( 0, CFile::begin ); CString cBuffer2; int iTotalTextLength = m_oChatMessageControl.GetWindowTextLength(); HWND focusWnd = ::GetFocus(); m_oChatMessageControl.HideSelection(TRUE, TRUE); m_oChatMessageControl.SetSel(iTotalTextLength, iTotalTextLength); cBuffer2 = m_oChatMessageControl.GetSelText(); LPTSTR...

Help with a formula..
I am trying to create a formula that will take information from a cell on one sheet and combine it with text on another sheet. I know how to get the two together. My problem is that I want the part that is brought in to be bolded type. Here is what I have in the formula. ="we are pleased to submit our quotation for "&(cell reference)&" according to the following specifications." What I want to do is have the cell reference part be bold type. Is there a way to do that? It doesnt work if I bold the cell.. already tried it.. Any suggestions? Thanks! KK You'...

Tracking Dates For Future Occurrences
Can this be done? I want to track a yearly review. I would like the date, once entered - say 6/1/2009, to conditionally format to change yellow 30 days before, then red 15 days before, and then to stay red until the date is updated again for say 6/1/2010. Can this be done? I am new to all this, thanks.. In 2007 Also.. "Knee2no" wrote: > Can this be done? I want to track a yearly review. I would like the date, > once entered - say 6/1/2009, to conditionally format to change yellow 30 days > before, then red 15 days before, and then to stay red until the date is...

Having problem of Outlook not receiving new e-mails
I have been using Outlook for over 1 year with Comcast Broadband. Suddenly last week, I received a message that Outlook 2000 was experiencing a problem when receiving new e-mails and was going to shut down. I re-opened Outlook and it looked different and then it asked for me set up a profile and I canceled it. But after that window coming up a couple of times after it shut down, I decided to put in a new name and then I could not get to my old e-mails and it acted like a new set up. Then I really got frustrated. I was eventaully able to get back to my old e-mails and have been able t...

Uninstalled Word 2004 test drive
I installed the "Test Drive" version of Microsoft Word 2004 on my ibook, G2, 500 mhz, machine. I am running Panther. After trying it for a couple days and becoming very frustrated with the program I followed all directions to remove the program ... I removed only the 2004 version. After uninstalling I could not automatically open any of my word documents. I never "created" any using the test drive, but I can�t open any of my documents created on my Word X version. Trying to open these documents I received an error message: "The operation could not be completed....

An Outlook 2003 Problem
I just upgraded from Outlook 97 to Outlook 2003. However, when trying to reply, forward, and compose with Outlook 2003, I got this error message: "The messaging interface has returned an unknown error. If the problem persists, restart Outlook." The problem persisted. I restarted Outlook 2003, but it didn't help. Any suggestions? Thanks. How did you upgrade Outlook 2003 from Outlook 97? Anyway If you use Hotmail or MSN mail, check MSKB info below. You receive a "The messaging interface has returned an unknown error " error message when you try to use Outlook to...

Problems Converting Data from Quicken 2001 Deluxe to MS Money
Hello, I have a relatively new Compaq Desktop (2.5 GHz Celeron with 512 MB RAM). I have a Viewsonic Pocket PC and I wanted to use it to track my financial data so I purchased Money 2003 Standard. I tried several times to convert my Qucken Data (it's a big file--I've been using Quicken since 1995). My Quicken program is Quicken 2001 Deluxe. Anyway, the MS Money program started to convert and after a few minutes said: "Your Quicken file could not be converted. Money could not convert your Quicken file. You might have run out of disk space or system memory. Try closing othe...

Sumproduct with multiple date criteria
Having a tough time with this one. Sheet 1 Column A = Start Date, Column B = End Date, Column C = Quantity. Sheet 2 Row A = Start Date, Row B = End Date. I would like Row C to sum quantity from sheet 1 where ever the two date ranges intersect. The date ranges on sheet 2 represent the beginning and ending of a week (Mon-Sun). Sheet 1 Column A Column B Column C 01JAN2010 24JAN2010 1,000 Sheet 2 Row A 04JAN2010 11JAN2010 18JAN2010 25JAN2010 Row B 10JAN2010 17JAN2010 24JAN2010 31JAN2010 Row c 1,000 1,000 1,000 0 Do the Sheet 2 Star...

Cell Format #4
Is there a way to have a cell format based on contents of an i statement... Example if(C1="Input",and(C3,Format $#.##),if(C1="% of Revenue",and(C5,Forma #.##%),na) I want the If statement to test a condition, return contents of th correct cell and format automatically. Any help is appreciated -- bforster ----------------------------------------------------------------------- bforster1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1177 View this thread: http://www.excelforum.com/showthread.php?threadid=26133 You can't change the fo...

Trouble doing a formula for excel
Hi All I have a spreadsheet with the following A1: z:\data/pc32/tsheets\unsorder00039.csv I would like to add 1 too the number to make unsorder00040.csv and so I have try mid,right,left i can't seem to do it Cheers "Jason" <Jason@discussions.microsoft.com> wrote in message news:53AAB904-8595-499F-BF38-8BE00826101C@microsoft.com... > Hi All > > I have a spreadsheet with the following > A1: z:\data/pc32/tsheets\unsorder00039.csv > > I would like to add 1 too the number to make unsorder00040.csv and so > I have try mid,right,left i can't see...

Exchange Server 2000 Secuirty Setup problems
Dear I have apply wrong setting to our exchange server 2000 . The wrong setting as from exchange manager . mail store , I have apply everyone send as and receive as access right, the group included remote access via offline sync. This setting once apply , all user access other mailbox without apply access right. I have try disable this setting, after all local office user no problems, but a remote offline sync laptop user does not work . The error as below: Unable to display the folder. Microsoft Outlook could not access the specified folder location. The file C:\exchange\shawnb.ost cannot b...

Formatting #13
Hi How can i have codes in this form 00.00.0000.00, & i wanted to sum to the values below like next code, 00.00.0000(+1).00 I'm tired to format but always sum in the last 2 digits 00.00.0000.00(+1), what can i do Someone can help me Thanks How did you put 00.00.0000.00 in the cell? Did you type 0 and then give it a custom format? If yes, try changing your custom format to: 00\.00\.\0000.00 Then add 1, but make sure that the resulting cell also has this custom format. This is really a funny formatted number with 2 decimal places now. Carla wrote: > > Hi, > How can i ...

CRM 4.0 Custom Report Filter Problem
I am using the Report Wizard to create a simple report. Report is using Quotes and Quote Products I have a custom field in Quote Products which is a bit field Yes-No When I use that field as a filter for report output, I get all records. The filter criteria appears to be ignored Is this an inherent problem with Report Wizard or Am I doing something wrong? Thanks. depends on your business logic and what you want to see. If you have three quotes: Quote-1 has three products, all with the custom field set to Yes Q2 has three products, two set to Yes, 1 to No Q3 has three products, all set...

Help! Problem Referencing Excel 2003 in VB.NET
I am using Visual Studio 2008 Express Edition to develop some VB.NET code that manipulates Excel files. I am developing on a Vista/Excel 2007 machine, but I need the code to work on an XP/2003 machine as well. I initially set a reference to the Microsoft Excel 12.0 Object Library and imported the Microsoft.Office.Interop.Excel namespace. This works great on the VIsta/Excel 2007 machine, but it does not work on 2003, which needs the 11.0 library. I get an error that says "Unhandled Exception . . . Could not file or assembly 'Microsoft.Office.Interop.Excel, Version 12.0.0....

copying formulas in vba
Hey guys. I was wondering if someone could help me. I am writing a vba script that takes in data, analyzes it, and then copies the results to a new file. I am having a problem with two things. 1) I am using a template for the new file so there are a lot of formulas (sums and std) already defined and ready to use. However, there are some instances where there is a random amount of additional data I have to put in. So, I have to apply the same formulas to this new data. How do I copy formulas from one cell to another (allowing for a change in row) in vba? Lets say cell(1,4) has the form...

conditional formatting in excel #3
how do you add a phrase to a field if the filed is blankl, also, can you have a notifiction sent to you when a date on a spreadsheet has expired? > how do you add a phrase to a field if the filed is blankl, What "phrase" do you mean? A Comment? A value? also, can you have > a notifiction sent to you when a date on a spreadsheet has expired? Maybe you can apply an open event (date to be tested being say in F1): Private Sub Workbook_Open() If Range("F1") < Date Then MsgBox "Date expired" End If End Sub Regards, Stefi ...

Duplicate personal folders problem
Hello all, I have a problem with Outlook 2003 and I hope someone can help me resolve it I imported a PST file from Outlook XP that was on a different machine, and now I have two versions of Personal Folders in my All Mail Folders list. They both have Deleted Items, Drafts, Inbox, Junk E-mail, Outbox, Sent Items and Search Folders in. One of them has an icon of a piece of paper with a clock to the top left and a house to the top right. The other one has an icon like a stack of files (like from a filing cabinet) They are clearly referencing the same thing, as the Deleted Items both have al...

Need Help with drop down list with conditions...
I need help. I'm having a very difficult time creating a sequence of list which change based on the item selected on the main list, lets call it the index: The Index list, the first list, would define vendors. In this case Hotel Companies (e.g Hilton, Marriott, Sheraton). The following list, the 2nd list, would define properties, but the properties would vary based on which hotel company is selected (e.g. If Hilton, then "The Plaza, the Waldorf Astoria. If Marriott, then Marriott Marquis, Marriott Convention Center etc etc...) on list #1. And Last but not least, the 3rd list would...

Converting date from an external source
I am having an issued with converting a date from external data source. the data has the timestamp in the general date form mm/dd/yyyy 00:00:00. I want to convert the date to mm/dd/yyyy format so when i run a query for a single day it will return the data for that date, I can currently return the data but i have to set the parameter in the mm/dd/yyyy 00:00:00 format, i want to simply return the data by setting the parameter in the mm/dd/yyyy format Don't confuse how data is stored with how it is presented. As long as you import the date into a field defined as a Date data type, you...

Excel formula #24
What is the formula that brings back a zero for an empty cell instead of 0 0 #DIV/0! Try =if(iserror(formula),0,formula) ************ Anne Troy www.OfficeArticles.com "Dave" <Dave@discussions.microsoft.com> wrote in message news:8392DE7F-0B65-4DEE-87F4-985133BB1976@microsoft.com... > What is the formula that brings back a zero for an empty cell instead of > 0 0 #DIV/0! > ...

Formatting Linked Cells
I have a project to do. I have to create an input worksheet that is the originator of other worksheets that are linked to the input worksheet. Is there a way to have the linked cells shown as a blank cell if the data (especially text data) is not enter in the input worksheet yet. MT Hi =IF(YourLink="","",YourLink) -- Arvi Laanemets (Don't use my reply address - it's spam-trap) "MT" <MT@discussions.microsoft.com> wrote in message news:5398D6F8-1554-46BB-B009-CCE3183C80ED@microsoft.com... > I have a project to do. I have to create an input ...

Excel Problem #3
I have win2000 with Office 2000 in the network! When i try to rename one excel file my computer make restart. This hapening not to all files,and all files there are in the same folder in the network! What hapen,and what i can do so as to work out the problem? I have all the required permissions on that network folder. No one else has any of the files open. They are not shared workbooks. ...

Problems with MFC list controls
I have a large list control with many inventory control items displayed within. When someone single clicks on an item, it displays information about that item in another part of the dialog. I have a three-fold problem, the master list has multiple columns in it, in order to make the selection work properly, only column 0 can be selected, is there a way to make a valid row selection if you select an item using any of the other columns? Second issue is the highlight of the selection. Only column 0 in the list control will highlight, is there a way to make the entire row highlight when an...