How to check date status in Excel

**Chini mao** · 07-01-2012

Hi all. I would be so grateful if anyone could help me with the following. I’ve searched threads and couldn’t 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

**GaganjyotTechie** · 07-01-2012

This is totally depends on 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.

**Chini mao** · 07-01-2012

Hi and thank you for offer of help. The color 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 colors IF there is no end date. Hope this makes more sense. Thank you

**GaganjyotTechie** · 07-01-2012

Hi and thank you for offer of help. The color 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 colors IF there is no end date. Hope this makes more sense. Thank you

Hey I think this is Supposed to work for you:

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 formulas at Conditional Formatting at Column A , so for that Select A2 to A9 and depends on the condition.
First Condition

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

And for 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.