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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I receive an error message for this formula " =AND($E$3-Today()<60,$E$3- Today()>0) ".
Please view screenshot.
Error-message.PNG
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)
Try with a semi-colon instead:
=AND($E$3-TODAY()<60;$E$3-
Christoforatos,
You don't need complicated formula like that. Just use stacked conditional format like my sample.
You don't need complicated formula like that. Just use stacked conditional format like my sample.
Hardly complicated formulas....
I think this is what you need.
Conditional-Format-Sample.xlsx