Well, I will take some blame for giving you a very brief answer with a
less
than adequate example. There are a couple key "warnings": first, make
sure
you put the equals sign before TODAY(). Without it Excel will compare the
cell to the word "TODAY()" instead of the formula for today's date.
Second,
the order in which the conditions are written is important, since Excel
will
apply them in order and once it meets one condition, that is the
formatting
that gets used.
Here's how I would do the whole conditional formatting (again, the [] just
shows the box to enter the formula:
Condition 1:
Cell Value Is between [=TODAY()+45] and [=TODAY()+31]
Set the format here to be the green
Condition 2:
Cell Value Is between [=TODAY()+30] and [=TODAY()+16]
Set the format here to be yellow
Condition 3:
Cell Value Is less than or equal to [=TODAY() + 15]
Set the format here to be red
If I understood you, this should give the colors you want.
And regarding the way I approached it: it is not a matter of right or
wrong
whether you choose to base it off the cell in A or in B, just preference.
My
head can figure the "time until deadline date" easier than it can do the
"time from 45 days before deadline", and so I felt more confident about
the
formula (should it be less than 15, or less than 16??? Always have to
think
that over to get it right...) - that is all! Well, that plus it enables
me
to use Cell Value Is instead of Formula Is...
Bob Reynolds said:
Thanks for your help, But I think I may have explained it wrong because
that
doesn't work.
Any days over 45 days show up as clear and from today +15 shows up as
green,
(not yellow like I said below) 16 to +30 yellow and greater than
=today()+31
is red.
It may very well be my inexperience doing this but any ideas on how I can
make it work???
And you are right, the color is the alert that the 45 day or more
deadline
is approaching..
Thanks
BOB
When I put this conditional formatting in and I use the colors below, I
get
just the reverse. If it's over 45 days it doesn't show at all and
K Dales said:
Make your conditions like this:
IF Cell Value is between [=TODAY()] and [=TODAY()+15]
where [] indicates the boxes where you type the conditions
This is slightly different than the way you worded the question because
I
am
looking at your A column values instead of B, but since they are just a
fixed
number of days apart it just means the condition is somewhat
different - I
am
looking at is as "am I less than 15 days away from the deadline???"
Easier
for me at least to conceptualize and get the formulas right!
:
Hello, I have a worksheet that cell A1 is calculated with a formula
=B1+45
and it is formatted to display as a date. The date entered into cell
B1
will
be displayed as 45 days ahead in A1.
I need to conditional format cell A1 to show a yellow background color
if
TODAY's date is is equal to or greater than B1 plus 15 days; Yellow
background if TODAY's date is equal to B1 plus 16 days but less than
B1
plus
30 days; and a Red background if TODAY's date is greater than B1+31...
I can't get the colors to change since the date in the cell A1 is
always
45
days.
Basically cell A1 tells me the 45 day drop dead date and I want the
cell
background color to give me visual warnings as to what stage the time
is
in.
Any ideas how I can make this work???
