Conditional Formatting: Excel 2007
I have a column called DEADLINE (Column B). I'm trying to use conditional formatting to color cells based on the deadline.
I want the cell to turn red if the dealdine is equal to today's date or it has already passed, turn yellow if the deadline is within 10 days of today's date, or green if today's date is more than 10 days away.
Here's what I tried but it's not working:
=B3>=(TODAY()+11) formatted to turn Green
=B3<=(TODAY()+10) formatted to turn Yellow
=B3<=(TODAY()) formatted to turn Red
Any help would be appreciated.
I want the cell to turn red if the dealdine is equal to today's date or it has already passed, turn yellow if the deadline is within 10 days of today's date, or green if today's date is more than 10 days away.
Here's what I tried but it's not working:
=B3>=(TODAY()+11) formatted to turn Green
=B3<=(TODAY()+10) formatted to turn Yellow
=B3<=(TODAY()) formatted to turn Red
Any help would be appreciated.
http://comp-tipsandtricks.blogspot.com/2007/03/conditional-color-formatting-in-excel.html
ASKER
Thanks Tripyre but this won't work becuse this only works for values in the cell. I actually need it to calculate a formula.
in conditional formatting the conditions are apied in order, so with the coditions you have all past dates will be yellow
Try switching conditions 2 and 3 (you can move them up and down with the arrows)
Regards, barry
Try switching conditions 2 and 3 (you can move them up and down with the arrows)
Regards, barry
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can also just set the Stop if True option for condition 2.
Hello Rory,
I'm not sure how that would work here. If the conditions are in the order given by the asker then past dates will still be formatted yellow.
In any case, if the formatting of the conditions is mutually exclusive, e.g. assuming green fill, yellow fill, red fill then "stop if true" effectively applies anyway even if not explicitly set.
What always annoys me about conditional formatting in Excel 2007 is that the first condition you set up actually defaults to the last condition on the list, so if the conditions are applied in the order shown they'll actually be placed in reverse order an that should work as required......
regards, barry
I'm not sure how that would work here. If the conditions are in the order given by the asker then past dates will still be formatted yellow.
In any case, if the formatting of the conditions is mutually exclusive, e.g. assuming green fill, yellow fill, red fill then "stop if true" effectively applies anyway even if not explicitly set.
What always annoys me about conditional formatting in Excel 2007 is that the first condition you set up actually defaults to the last condition on the list, so if the conditions are applied in the order shown they'll actually be placed in reverse order an that should work as required......
regards, barry
Barry,
You're quite right - brain fart on my part. Definitely pub time for me... :)
You're quite right - brain fart on my part. Definitely pub time for me... :)