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