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
146 Views

Similar Articles

[PageSpeed] 6

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:

Date format issue when submitting from a userform to a spreadsheet
Hi, I have a userform that I've generated which routes dates onto a spreadsheet based on the users input. I am having a bit of a frustrating time with the dates, it would appear that in the process of moving the date from the userform to the spreadsheet some dates are switched/transposed. I'll give an example. If someone enters 09/02/2004 on the userform excel seems to look at 09 and assume it is a month and transposes the dates to 02/09/2004(this does not appear to be a US/UK format issues as I have already gone down that road). However, if the date 13/09/2004 is entered i...

Please help..with a formula. I don't know code.
I have a long list of numbers - values in a file X, and I want to fin and replace those values in a even larger list in a file Z an highlight those values in Z -- Message posted from http://www.ExcelForum.com Hi not really sure what you're trying to achieve. What do you want to replace, etc. You may give an example (plain text - no attachment please) >-----Original Message----- >I have a long list of numbers - values in a file X, and I want to find >and replace those values in a even larger list in a file Z and >highlight those values in Z. > > >--- >Message...

Custom cell formatting
I need to create a custom format for a series of cells that will begin like this. I can't figure out what the code character is fora volitile potentially alpha character. Can anyone help me?? -Monica, Dallas 000000 000001 000002 .... 000009 00000A 00000B 000010 000011 ...... Monica something like this might work for you but you'll need to put all the leading digits in for the entry with the alpha character Regards Trevor "MDavison" <davison@fr.com> wrote in message news:#SD0tUzTEHA.1652@TK2MSFTNGP09.phx.gbl... > I need to create a custom format for a series ...

Auto formatting features: How do I align page numbers in publicati
I've got a problem with my publication. I can't align even page numbers to the left without automatic moving the odd numbers to the left as well? Can someone help me? Cissy99 wrote: > I've got a problem with my publication. I can't align even page numbers to > the left without automatic moving the odd numbers to the left as well? Can > someone help me? You need to create a two-page master rather than a one-page master. What version of Publisher are you running? -- Ed Bennett - MVP Microsoft Publisher http://ed.mvps.org ...

Need help with formula 01-13-10
I am trying to adapt a formula in I2 from another spreadsheet that works well, but won't in mine. I've traced the error, but I would need help to understand the help it gives! My formula is this: =IF(J2="0-Jan-00","To be advised",WORKDAY(J2,1,NWD)). I have a worksheet in the same workbook with a list of non-workdays, and defined the column of dates with the name "NWD". What I expect the formula to do is this: If J2 is Feb. 4, it would give Feb. 5 in cell I2 because Feb. 5 is NOT a non-workday in NWD. But if J2 is Feb. 5, and Feb. 6 and...

Keyboard shortcut for current date and time
Hi, Ctrl+ ; inserts current date and Ctrl+Shift+; inserts current ti me Ctrl+Shift+; inserts the current time with the date serial as 0 and not the current date's date serial. Presently I am adding the two (ie current date and time) to get the current date and time. Is there a keyboard shortcut that does this? Thanks in advance. Regards, Raj CTRL+; then SPACE then CTRL+SHIFT+; -or- =Now() -- Jim Cone Portland, Oregon USA http://www.mediafire.com/PrimitiveSoftware "Raj" <rspai9@gmail.com> wrote in message Hi, Ctrl+ ; inserts current dat...

how to turn off automatic format in Excel?
Hi Excel automatically change the first character in a cell to be uppercase. I just want lowercase. How can I turn off this function? Thanks Ngoc Hi Go to Tools / Autocorrect and uncheck Capitalize first letter of sentence. Andy. "ngoc" <linh@chello.no> wrote in message news:BOKNb.271$O41.819@amstwist00... > Hi > Excel automatically change the first character in a cell to be > uppercase. I just want lowercase. How can I turn off this function? > Thanks > Ngoc > ...

Formatting
I've always used MS Word as my email editor in Outlook. Is this possible in Live Mail. Nope. I'm afraid that's only possible using Outlook. In WLM you have to use the built in editor. Is there anything in particular that's lacking from the built in editor that you're looking for? Colin Brown WL MVP "jrchambe" <jrchambe@discussions.microsoft.com> wrote in message news:5EC21892-D39F-4219-AB0F-47BC14E1CD36@microsoft.com... > I've always used MS Word as my email editor in Outlook. Is this possible > in > Live Mail. "...

Font problem
It seems that the Cambria Math font has recently acquired an extremely large Ascent and Descent. Has it perhaps always been that way and I've never noticed it before, or is it something recent, perhaps a Vista thing? Try the following code for example. For Arial and Times New Roman (and virtually all other fonts) I am getting pretty much exactly what I would expect (a TextHeight that is a bit larger than the point size) but for Cambria Math I am getting an extremely large TextHeight (I get exactly the same results using GDI32 methods). At this end (on my Vista Business laptop...

Questiontest-outlookwebservices problem
hi all and thanks for the support. i'm facing a problem with test.outlookwebservices. notice that oulook side everything seems to be pretty good: the autodiscovery and auto-configuration of the account succeedes, the test email autoconfiguration suceedes too. when i launch the test, once it suceedes for certain services and after a while it fails for those services and suceedes for other. look the prints: here are failing the RPC tests Message : [EXCH] The UM is configured for this user in the AutoDiscover response received from https://mytest-EXC1. myt...

08 Mac Office activation problems
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Just found an article posted by michellec and I am faced with exactly the same problem - on 'activation' of the product, none of the 3 product keys seem to be correct, hence activation is unsuccessful!! Have received some help emails (and thanks Diana yours had the microsoft help number that has been good, despite the fact they can't help me and tell me I must get in touch with Digital River) but seems that what I need is another product key, and now that I'm asking for that since about 10 days, I don't get any reply ...

Problem with systemstate backup with DPM 2010
Hi! We running DPM 2010RC on a Windows 2008 R2 The Protected computer is: Microsoft(R) Windows(R) Server 2003, Standard Edition 5.2.3790 Service Pack 2 Build 3790 When we installed the DPM agent the first backup of system state was successfully, so we have one restore point. But further backups in failing, the system state backup is in status “replica is inconsistent” and when I running a consistent check the job failing. In the error in DPM is: The replica of System Protection Computer\System Protection on sea0700smon1.xxxx.local is inconsistent with the protected data so...

Fewer normal template problems with Word 2000 or 2002?
I have enjoyed some things about Word 2007, but I keep having problems with macros and templates. I am wondering if (a) macros increase the likelihood of corrupting the normal template in 2007, or (b) if the older versions of Word had as many template problems. I have used Word in 2000, with Office 2003, and now with Office 2007 and I don't ever recall having as many problems as I've had with 2007. What I'm thinking is that if it is simply a macro problem causing corruption, I would simply use 2007 without the macros (they're nice but I can live without them). On the...

Background changes conditionally
Hi all, I have a spreadsheet that shows the floor plan layout of my call centre. On each desk, the desk number and identity are displayed. I would like to colour the background for those pcs that are on the same subnet. I have a sheet called data - it contains Table Number, Desk Number, PC ID, IP Address. How could I get it to change the background colour if the pc is on subnet 162, and set a different colour for those on subnet 167 any ideas? -- PeterG ------------------------------------------------------------------------ PeterG's Profile: http://www.excelforum.com/member.p...

Formatting Cells in Excel 97
Hi Guys, Sorry if I sound real stupid but is there anyway that we can control the column formatting in Excel 97 like let's say column A = GENERAL(6), column B = GENERAL(4), column C = TEXT(18) etc? Appreciate any form of advice, thanks!! I replied in the programming group. Are you seeking a programming answer? It's usually best to only post to one group, and include the remark "Please tell me if I should ask this in another group." On Thu, 28 Aug 2003 00:56:47 -0700, "Daryl" <daryl.ho@tnt.com> wrote: >Hi Guys, > >Sorry if I sound real stupid b...

using dates Part 2
Karl was great in helping me get to this point with dates, now I'm wondering if we can take it 1 step further? For Activity Dates prior to 2/1/2007 they are using a normal reporting year and the formulas below take care of Activity dates >2/1/2007? So for example prior to 2/1/2007 1/1/2006 would have a B_Qtr of 2006-1 1) B_Qtr - 2011-1 --- Format(DateAdd("m",11,[ActivityDate]), "yyyy - q") 2) Year - 2011 ---- Year(DateAdd("m",11,[ActivityDate])) 3) Qtr - Q1 ---- Format(DateAdd("m",11,[ActivityDate]), "q") -- Than...

stop automatically changing formula!
i have a countif function COUNTIF(Locking!I16:I40,"f") when i copy this and paste it to the next cell, the formula automatically change to COUNTIF(Locking!J16:J40,"f") How do I stop it from changing column I to J?!?!?! thanks. Caryn, =COUNTIF(Locking!$I$16:$I$40,"f") or =COUNTIF(Locking!$I16:$I40,"f") HTH, Bernie MS Excel MVP "caryn" <caryn.tan@gmail.com> wrote in message news:d7n4u3$hgi$1@avnika.corp.mot.com... > i have a countif function > COUNTIF(Locking!I16:I40,"f") > when i copy this and paste it to the nex...

Getting Cell Value from the Concatenate formula
I built a concatenate formula that returns the following result: =Jul! $D27 I am looking for the cell contents of Sheet: July Column D Row 27. I tried to use offset, but I am stumped. Can I add something to the front of the concatenate to not only build the reference to the cell, but also return the value instead of the =Jul!$D27 ? Thanks John =indirect(yourformulahere) Don't include the equal sign in your formula. And match the name correctly (Jul or July???). Depending on the name of the worksheet, you may need to have a string that looks like: 'Sheet 99'!d27 =indirec...

format a CD
Hi, how can I format a cd+rw re writeable disc aga ...

Autoshapes' visible problem
When I add some Rectangle autoshapes above the embeded chart, I find it's hard to control it's visibility, They maybe hide when I select some cell or activate chart. I want to know how to make them always visible no matter what I select. Thank you in advance! ^_^ Yours,fujing You need to click on the chart before you use the drawing tool. In this way the Shape and the Chart become one. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "fujing1003" <fujing1003@gmail.com> wrote in message news:1171358292.074278.135950@v33g2000cwv...

PivotTable Formulas
I have set up a pivot table. I have been asked to add a column at the end of the pivot table that calculated the %variance of sales from this year versus last year. I tried to create the formula (Calculate Field) after I had grouped my dates for Months & Years. I had to ungroup these to be able to create the formula. I do not see how when I have ungrouped the date to distinguish between 2003 & 2002 in my Pivot formula. Is this possible of am I flogging a dead horse here? Any suggestions would be most help ful Regards GarethG ------------------------------------------------ ~~...

RMS and/or PC Charge Problems
We are having some interesting problems with our RMS system and PC Charge: For no apparent reason RMS will have an error message after a card is swiped or keyed into the system: • DENIED (With a wide variety of error messages) • DUPLICATE use F+ to force (even when it is the first transaction) • TIMED OUT Attempted solutions: • Turned modem off and on • Disconnected the modem and plugged everything back in • Turned the server on/off • Called PC Charge only to be told that it is RMS • RMS said it is PC Charge • Confirmed that everything is set up properly in PC Charge and RMS What works:...

Office 2004 SP 11.1.0 installation problems
I'm trying to update Office 2004 on my brand new iBook. I installed Office 2004 from the CD without incident. Then I downloaded SP 11.1.1.0 and 11.1.0 (which according to the MS website is to be installed 1st.) When I try to install, I received a message, "An error prevented the update from completing 11002:2,-14" The "Read Me" file indicates several potential explainatins/solutions none of whice are relevant except for uninstalling Office and then reinstalling it. I tried dragging the Office folder to the trash and reinstalled and am having the same problem. I've t...

Remove format link?
Is there a way to remove the format link between fields in Modifier? Help just shows how to set it and the usual stuff doesn't seem to be working. ...

Making a template that puts the current date in the document so that does NOT change
I'd like to make a template that sets up some standard headers and formatting for new Word documents for a night school course I'm enrolled in. Among other requirements for all papers is to put the date the paper was created at the top. I'd furthermore like it so that if I need to reopen the document after creating it to say print another copy, the date written at the top will not change. In other words, when I create a new document using the template the current date is put near the top, but when I subsequently open the file for editing or reprinting it does NOT automat...