Thread: rag status help
View Single Post
  #2   Report Post  
Mazzaropi Mazzaropi is offline
Senior Member
 
Location: Belo Horizonte, Brazil
Posts: 170
Thumbs up

Quote:
Originally Posted by lp78 View Post
hi and thank you for your offer of help.

The colour status is as you suggest but to change from the start date plus 90 days for amber and 180 days for red. I only want the start date to change colours IF there is no end date .
Hope this makes more sense. Thank you
-------------------------------------------------------------------------

Dear LP78, Good Evening.

Suppose this:

CURRENT DATE: FEB 16, 2011

___A____________B__________C
1__START DATE___END DATE
2__11/19/2010
3__11/18/2010
4___8/21/2010
5___8/20/2010
6___8/19/2010
7__11/17/2010____12/12/2010
8___8/21/2010
9___8/20/2010____10/20/2010


Put these fórmulas at Conditional Formatting at Column A

Select A2 to A9

First Condition
Formula -- =AND(TODAY()-A2=90,TODAY()-A2<180,B2="")
Format --- AMBER

Second Condition
Formula -- =AND(TODAY()-A2<=180,B2="")
Format --- RED

The result must be:

___A____________B__________C
1__START DATE___END DATE
2__NO CHANGE
3__AMBER
4__AMBER
5__RED
6__RED
7__NO CHANGE____12/12/2010
8__AMBER
9__NO CHANGE____10/20/2010


Please, do this and tell me if it worked for you.
__________________
I hope it can help you.

Best regards,
Marcilio Lobão
---------------------------
Belo Horizonte, Brazil