conditioning

D

DONNA

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

Ian

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)
 
B

bpeltzer

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
 
I

Ian

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

Ragdyer

=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 said:
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
Click to expand...
THANKS
 
D

DONNA

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

DOR

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

DONNA

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