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

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
Asked:
christoforatos1968
  • 3
  • 2
1 Solution
 
NBVCCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now