Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
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. |
#2
![]() |
|||
|
|||
![]()
Sorry, I hit the post button too soon....
Thanks for your help! Russ |
#3
![]() |
|||
|
|||
![]()
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. . |
#4
![]() |
|||
|
|||
![]()
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 |
#5
![]() |
|||
|
|||
![]()
This seemed to work for me:
=AND(B1="",TODAY()-A1120) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== wrote in message ups.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 |
#6
![]() |
|||
|
|||
![]()
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 |
#7
![]() |
|||
|
|||
![]()
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 ! -------------------------------------------------------------------- wrote in message oups.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 |
#8
![]() |
|||
|
|||
![]()
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 |
#9
![]() |
|||
|
|||
![]()
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
How to preserve conditional formatting on a web query table result | Excel Discussion (Misc queries) | |||
conditional formatting conflict? | Excel Worksheet Functions | |||
How do I get more than 3 Conditional formatting? | Excel Discussion (Misc queries) | |||
conditional formatting | Excel Worksheet Functions | |||
How do I use conditional formatting for multiple rows? | Excel Worksheet Functions |