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.

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

Thanks for your help!

Russ

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.
>
>.
>

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

This seemed to work for me:

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

HTH,

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

<[email protected]> wrote in message
news:[email protected]...
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

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

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 !
--------------------------------------------------------------------


<[email protected]> wrote in message
news:[email protected]...
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

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

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