Firefox Extension: How to check date status in Excel

How to check date status in Excel
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
Reply With Quote
#2
Old 07-01-2012
GaganjyotTechie GaganjyotTechie is offline
Member

Join Date: May 2011
Posts: 433
Re: How to check date status in Excel
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.
Reply With Quote
#3
Old 07-01-2012
Chini mao Chini mao is offline
Member

Join Date: Dec 2011
Posts: 64
Re: How to check date status in Excel
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
Reply With Quote
#4
Old 07-01-2012
GaganjyotTechie GaganjyotTechie is offline
Member

Join Date: May 2011
Posts: 433
Re: How to check date status in Excel
Quote:
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
Quote:
Formula --> =AND(TODAY()-A2>=90,TODAY()-A2<180,B2="") Format ---> AMBER
And for second Condition
Quote:
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.
Reply With Quote
#5
Old 07-01-2012
Chini mao Chini mao is offline
Member

Join Date: Dec 2011
Posts: 64
Re: How to check date status in Excel
You are an absolute star! That has worked a treat and made things so much easier. Many thanks.