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.
christoforatos1968Asked:
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:

=AND($E$3-Today()<60,$E$3-Today()>0)

click Format and choose Amber from Fill tab.

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

=$E$3=TODAY()

and click Format and choose RED

Then Ok, New Rule and

=$E$3-TODAY()>60

and choose Green from the Fill tab.

click Ok, then Ok again to end

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

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

With monday.com’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.

 
NBVCCommented:
It might be because of your Excel version...

Try with a semi-colon instead:

=AND($E$3-TODAY()<60;$E$3-TODAY()>0)
0
 
Harry LeeCommented:
Christoforatos,

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

From novice to tech pro — start learning today.