Conditional Format based on date ranges

[Corey]

I am trying to set the following to have conditional formatting:

>Current date < Current date+14 days = Green shade
>Current date+15<Currentdate+30=Yellow shade
<=Current date=Red shade

I am having trouble with the Between formulas.
I have tried something like:
Cell value is BETWEEN: [NOW()] and [NOW()+14] Green shade
Cell value is BETWEEN: [NOW()+15] and [NOW()+30] Yellow shade
Cell value is GREATER THAN: [NOW()+31] Red shade

But i get no CF?

The values ARE a calculated DATE value, Does this mean i should use the
FORMULA IS instead of the Cell is?

I then ONLY get Value is and Not the option of IS BETWEEN....


Any ideas
i am sure it is a simple formula sintax error i am doing wrong
Regards

Corey

[daddylonglegs]

The order in which you apply the conditions is important. You can use "formula is" but "cell value is" works fine too. Use something like this

condition 1
"cell value is" Greater than =TODAY()+30
red format

condition 2
"cell value is" Greater than =TODAY()+14
yellow format

condition 3
"cell value is" Greater than =TODAY()
green format

[Corey]

Thanks for the reply.
I have entered as you suggested, but i do not get ANY shading at all??


--
Regards

Corey

"daddylonglegs" <[email protected]>
wrote in message
news:[email protected]...
>
> The order in which you apply the conditions is important. You can use
> "formula is" but "cell value is" works fine too. Use something like
> this
>
> condition 1
> "cell value is" Greater than =TODAY()+30
> red format
>
> condition 2
> "cell value is" Greater than =TODAY()+14
> yellow format
>
> condition 3
> "cell value is" Greater than =TODAY()
> green format
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile:
> http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=566469
>

[WLMPilot]

Here are the formulas you need. I made a couple of adjustments. Day 14 & 15
get overlooked according to your formulas and if that is what you want, then
adjust the formulas I send you. I am using A1 as the cell with the date you
want to check:

1st) =AND(A1<>"", A1>TODAY(), A1<=TODAY()+14)
2nd) =AND(A1<>"", A1>TODAY()+14, A1<=TODAY()+30)
3rd) =AND(A1<>"", A1<=TODAY())

"Corey" wrote:

> I am trying to set the following to have conditional formatting:
>
> >Current date < Current date+14 days = Green shade
> >Current date+15<Currentdate+30=Yellow shade
> <=Current date=Red shade
>
> I am having trouble with the Between formulas.
> I have tried something like:
> Cell value is BETWEEN: [NOW()] and [NOW()+14] Green shade
> Cell value is BETWEEN: [NOW()+15] and [NOW()+30] Yellow shade
> Cell value is GREATER THAN: [NOW()+31] Red shade
>
> But i get no CF?
>
> The values ARE a calculated DATE value, Does this mean i should use the
> FORMULA IS instead of the Cell is?
>
> I then ONLY get Value is and Not the option of IS BETWEEN....
>
>
> Any ideas
> i am sure it is a simple formula sintax error i am doing wrong
> Regards
>
> Corey
>
>
>

[DonCam65]

The following works for me
Select the first cell (calculated or not - it makes no difference) for the
conditional formatting

Format
Conditional Formatting
Condition 1
<Cell Value> between =now()-1 and =now()+14
format to required green
Add condition 2
<Cell Value> between =now()+15 and =now()+30
format to required yellow
Add condition 3
<Cell Value> greater than =now()+30
format to required red

(Note that in the conditions there are no spaces)

OK
Click on FORMAT PAINTER
Highlight all the cells the formatting is to apply to

Hope this solves your problem

Don C


"Corey" wrote:

> Thanks for the reply.
> I have entered as you suggested, but i do not get ANY shading at all??
>
>
> --
> Regards
>
> Corey
>
> "daddylonglegs" <[email protected]>
> wrote in message
> news:[email protected]...
> >
> > The order in which you apply the conditions is important. You can use
> > "formula is" but "cell value is" works fine too. Use something like
> > this
> >
> > condition 1
> > "cell value is" Greater than =TODAY()+30
> > red format
> >
> > condition 2
> > "cell value is" Greater than =TODAY()+14
> > yellow format
> >
> > condition 3
> > "cell value is" Greater than =TODAY()
> > green format
> >
> >
> > --
> > daddylonglegs
> > ------------------------------------------------------------------------
> > daddylonglegs's Profile:
> > http://www.excelforum.com/member.php...o&userid=30486
> > View this thread: http://www.excelforum.com/showthread...hreadid=566469
> >
>
>
>