Program expiration dates in excel

I like to create an excel file, where the days available between date certified and due date, will give me the results in red, amber, or green color.
Explanation below:
Number of days since certification  XX DAYS             CELL 1:E
Due expiration date                         4/8/2014            CELL 2:E
Date certified                                   4/8/2013            CELL 3:E
                                                                                     CELL 4:E
"Number of days since certification" in CELL 1:E will change from the result of "Date certified" in CELL 3:E and the current Date.
60 days before "Due expiration date" i need cell 4:E to become amber.
On "Due expiration date", i need cell 4:E to become red.
All other days before "Due expiration date", cell 4:E will be green.
Also CELL 4:E will show the amount of days left leading up to the expiration date.

Can someone give me feedback on this.
Who is Participating?
NBVCConnect With a Mentor Commented:
Select cell E4, and go to Home|Conditional Formatting|New Rule

Select "Use a formula to determine which cells to format" from top section, then enter formula:


click Format and choose Amber from Fill tab.

Click Ok, then click New Rule and repeat above with formula:


and click Format and choose RED

Then Ok, New Rule and


and choose Green from the Fill tab.

click Ok, then Ok again to end

In Cell E4 you can use formula: =$E$3-TODAY()
Harry LeeCommented:
Please see the sample.

I think this is what you need.
christoforatos1968Author Commented:
I receive an error message for this formula " =AND($E$3-Today()<60,$E$3-Today()>0) ".
Please view screenshot.
The new generation of project management tools

With’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

It might be because of your Excel version...

Try with a semi-colon instead:

Harry LeeCommented:

You don't need complicated formula like that. Just use stacked conditional format like my sample.
Hardly complicated formulas....
All Courses

From novice to tech pro — start learning today.