hi all. I would be so grateful if anyone could help me with the following. Ive searched threads and couldnt find what i need. i have an excel spreadsheet which has start dates ( both old and current) which i need to turn amber if 90 days has past and red if 180 days has past if there is no end date in column L . Many thanks

-------------------------------------------------------------------------

Dear LP78, Good Evening.

Could you explain better your necessity?


Colour_______Status
AMBER_______90 to 179 days has past from START DATE until TODAY

RED_________180 or MORE days has past from START DATE until TODAY

????_________if there is no end date in column L


Please, explain your necessity. I believe that I can help you.

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.

ur an absolute star! That has worked a treat and made things so much easier. Many many thanx.
I wounder if i could push my luck. I would like to produce a graph using this rag data. Is there a formula a could use so that it would only pick up those whose status is red?

--------------------------------------------------------------------------

Dear LP78, Good Afternoon.

To do a graph is possible, BUT what kind of graph?
What kind of data you need to show up?

"...Is there a formula a could use so that it would only pick up those whose status is red?..."

Do you need quantity; Do you need Details???

Please, Once more, explain better and with details your necessity.
Give an example, if possible.

hi again and thank you for ur patience with me. I currently produce several graphs so i can manage that part. Wat i was woundering is if it would b possible to create a sort of livin table. Ie i would like cell Z2 to automatically populate the number of cells in column A with a red status only and like wise Z3 to show those with an amber status. Is this possible?
Thank you again.