conditioning

I have a spreadsheet. I need a cell to turn
yellow is date is on target,
green if date is ahead of target date
RED if date is past target date.
AND I NEED THE CELL TO STAY WHITE IF I DO NOT PUT A DATE IN CELL.
It seems to me that I need four conditions. PLEASE HELP IMMEDIATELY. THANKS

Assuming your cell is B2 and the target date is in A2. Use "Formula is" and
enter

for on target
=AND(B2<>"",B2=A2)
for ahead
=AND(B2<>"",B2<A2)
for past
=AND(B2<>"",B2>A2)

--
Ian
--
"DONNA" <[email protected]> wrote in message
news:[email protected]...
>I have a spreadsheet. I need a cell to turn
> yellow is date is on target,
> green if date is ahead of target date
> RED if date is past target date.
> AND I NEED THE CELL TO STAY WHITE IF I DO NOT PUT A DATE IN CELL.
> It seems to me that I need four conditions. PLEASE HELP IMMEDIATELY.
> THANKS

You actually get an implicit fourth condition, which is 'none of the above'.
As long as your three conditions are specified such that they're not
satisfied when no date is entered, you should be fine. That probably means
you can't use a strict 'less than' condition; you'd have to use 'between'
instead.
--Bruce

"DONNA" wrote:

> I have a spreadsheet. I need a cell to turn
> yellow is date is on target,
> green if date is ahead of target date
> RED if date is past target date.
> AND I NEED THE CELL TO STAY WHITE IF I DO NOT PUT A DATE IN CELL.
> It seems to me that I need four conditions. PLEASE HELP IMMEDIATELY. THANKS

I agree. This simplifies the formulae I posted. I had forgotten that it is
not necessary to check for a blank cell for the on target and past target
dates, reducing these to

for on target
=B2=A2
for past
=B2>A2

For ahead, use Cell value is>between and use 1 and A2 as the limits.

--
Ian
--
"bpeltzer" <[email protected]> wrote in message
news:[email protected]...
> You actually get an implicit fourth condition, which is 'none of the
> above'.
> As long as your three conditions are specified such that they're not
> satisfied when no date is entered, you should be fine. That probably
> means
> you can't use a strict 'less than' condition; you'd have to use 'between'
> instead.
> --Bruce
>
> "DONNA" wrote:
>
>> I have a spreadsheet. I need a cell to turn
>> yellow is date is on target,
>> green if date is ahead of target date
>> RED if date is past target date.
>> AND I NEED THE CELL TO STAY WHITE IF I DO NOT PUT A DATE IN CELL.
>> It seems to me that I need four conditions. PLEASE HELP IMMEDIATELY.
>> THANKS

=AND(ISNUMBER(B2),B2>A2)

Permits text entries in normal color and late dates in red.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"bpeltzer" <[email protected]> wrote in message
news:[email protected]...
> You actually get an implicit fourth condition, which is 'none of the
above'.
> As long as your three conditions are specified such that they're not
> satisfied when no date is entered, you should be fine. That probably
means
> you can't use a strict 'less than' condition; you'd have to use 'between'
> instead.
> --Bruce
>
> "DONNA" wrote:
>
> > I have a spreadsheet. I need a cell to turn
> > yellow is date is on target,
> > green if date is ahead of target date
> > RED if date is past target date.
> > AND I NEED THE CELL TO STAY WHITE IF I DO NOT PUT A DATE IN CELL.
> > It seems to me that I need four conditions. PLEASE HELP IMMEDIATELY.
THANKS

I have:

E3 F3 G3
Start Date Target date Completion date
1/01/05 1/30/05 2/01/05 (cell red,
after target)

WHEN I ENTER TARGET DATE, G3 TURNS GREEN. I NEED G3 TO "ONLY" TURN A COLOR
"IF" A DATE IS ENTERED IN G3. THE DATE ENTERED IN G3 WILL DETERMINE WHAT
COLOR G3 WILL BE.
AGAIN, PROBLEM IS: G3 TURNS A COLOR WHEN I ENTER DATE IN F3. I WANT G3 TO BE
"NO" COLOR UNTIL A DATE IS ENTERED.



"Ian" wrote:

> Assuming your cell is B2 and the target date is in A2. Use "Formula is" and
> enter
>
> for on target
> =AND(B2<>"",B2=A2)
> for ahead
> =AND(B2<>"",B2<A2)
> for past
> =AND(B2<>"",B2>A2)
>
> --
> Ian
> --
> "DONNA" <[email protected]> wrote in message
> news:[email protected]...
> >I have a spreadsheet. I need a cell to turn
> > yellow is date is on target,
> > green if date is ahead of target date
> > RED if date is past target date.
> > AND I NEED THE CELL TO STAY WHITE IF I DO NOT PUT A DATE IN CELL.
> > It seems to me that I need four conditions. PLEASE HELP IMMEDIATELY.
> > THANKS
>
>
>

Use the following conditions:

=G3>F3 -->red
=G3=F3) -->yellow
=AND(G3<>"",G3<F3) -->green

You might also use data validation on the date fields to ensure that
only dates are entered in them ...

Thanks DOR,
I hope this formula works. SHOULD I TYPE THE WORDS RED IN THE FORMULA OR ARE
YOU TELLING ME TO MAKE THE FORMAT RED, YELLOW GREEN?????

"DOR" wrote:

> Use the following conditions:
>
> =G3>F3 -->red
> =G3=F3) -->yellow
> =AND(G3<>"",G3<F3) -->green
>
> You might also use data validation on the date fields to ensure that
> only dates are entered in them ...
>
>

THANKS A BUNCH. THIS FORMULA WORKED PERFECT. YOU ARE A LIFE SAVER.

"DOR" wrote:

> Use the following conditions:
>
> =G3>F3 -->red
> =G3=F3) -->yellow
> =AND(G3<>"",G3<F3) -->green
>
> You might also use data validation on the date fields to ensure that
> only dates are entered in them ...
>
>