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

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!

"Bob Reynolds" wrote:

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

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" wrote in message
...
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!

"Bob Reynolds" wrote:

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

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" wrote:

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" wrote in message
...
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!

"Bob Reynolds" wrote:

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

Thank you so much for your help, the only change I had to make was the order
of the colors, I had to reverse them. They work great
and I can't thank you enough. I'm confused but you got me fixed... Thanks

BOB

"K Dales" wrote in message
...
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" wrote:

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" wrote in message
...
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!

"Bob Reynolds" wrote:

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