Conditional Formatting
Posted by Dee on February 05, 2002 6:37 AM
I have a worksheet with various columns. In these I have one for the date I sent out something to be approved. In the next column I have a place for when it was returned approved. The next column is for how many days it is out and the next column is for how many days it took. Question is this. In the days out column I have a condition set that shows different colors depending on how many days it's been out. But, I would like this condition to go away if there is a date in the returned column. Is this possible?
Posted by Mark W. on February 05, 2002 7:09 AM
Use a "Formula Is" condition with...
=AND(ISNUMBER(Return_Dt),'Days Out'=value)
...where 'Return_Dt' is a cell reference to
the Return Date and 'Days Out' is a cell reference
to the number of days.
Posted by Mark W. on February 05, 2002 7:11 AM
Oops! Misread your request...use this instead...
=AND(ISBLANK(Return_Dt),'Days Out'=value)
Posted by Dee on February 05, 2002 8:00 AM
Here is my formula, can you help with changes needed?
I am using this formula right now. D19 is the day they were sent out for approval. How can I use your formula in this? E19 would be the cell that is for Back from Approval. and F19 is where this formula is at. Can you help with this?
=IF(ISBLANK(D19),"",DATEDIF(D19,NOW(),"d"))
Posted by Mark W. on February 05, 2002 8:13 AM
Posted by Dee on February 05, 2002 8:15 AM
Conditonal Formating is 3 as listed.
Cell Between 1 and 14 shaded Yellow,
Cell Between 15 and 19 shaded Orange, and
Cell Between 20 and 100 Shaded Red.
Posted by Mark W. on February 05, 2002 9:37 AM
Change your condition to a "Formula Is" condition
and use...
=AND(ISBLANK(A2),B2>=1,B2< =14) for Yellow
=AND(ISBLANK(A2),B2>=15,B2< =19) for Orange
=AND(ISBLANK(A2),B2>=20,B2< =100) for Red
Where A2 contains your Return Date and B2
contains your 'Days Out' value. Note: when
you enter these remove the space from "< =".
I added the space to overcome a problem with
this website.
