Link to home
Start Free TrialLog in
Avatar of christoforatos1968
christoforatos1968

asked on

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.
ASKER CERTIFIED SOLUTION
Avatar of NBVC
NBVC
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Please see the sample.

I think this is what you need.
Conditional-Format-Sample.xlsx
Avatar of christoforatos1968
christoforatos1968

ASKER

I receive an error message for this formula " =AND($E$3-Today()<60,$E$3-Today()>0) ".
Please view screenshot.
Error-message.PNG
It might be because of your Excel version...

Try with a semi-colon instead:

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

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