• Status: Solved
• Priority: Medium
• Security: Public
• Views: 433

# 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.
0
christoforatos1968
• 3
• 2
1 Solution

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

Commented:

I think this is what you need.
Conditional-Format-Sample.xlsx
0

Author Commented:
I receive an error message for this formula " =AND(\$E\$3-Today()<60,\$E\$3-Today()>0) ".
Error-message.PNG
0

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

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

Commented:
Christoforatos,

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

Commented:
Hardly complicated formulas....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.