Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
conditioning
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<"",B2A2) -- Ian -- "DONNA" wrote in message ... 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 |
#3
|
|||
|
|||
conditioning
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 |
#4
|
|||
|
|||
conditioning
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 =B2A2 For ahead, use Cell value isbetween and use 1 and A2 as the limits. -- Ian -- "bpeltzer" wrote in message ... 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 |
#5
|
|||
|
|||
conditioning
=AND(ISNUMBER(B2),B2A2)
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" wrote in message ... 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 |
#6
|
|||
|
|||
conditioning
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<"",B2A2) -- Ian -- "DONNA" wrote in message ... 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 |
#7
|
|||
|
|||
conditioning
Use the following conditions:
=G3F3 --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 ... |
#8
|
|||
|
|||
conditioning
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: =G3F3 --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 ... |
#9
|
|||
|
|||
conditioning
THANKS A BUNCH. THIS FORMULA WORKED PERFECT. YOU ARE A LIFE SAVER.
"DOR" wrote: Use the following conditions: =G3F3 --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 ... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date conditioning format problem | Excel Discussion (Misc queries) | |||
help with cell conditioning? | Excel Worksheet Functions |