Solved

Program expiration dates in excel

Posted on 2014-01-02
6
411 Views
Last Modified: 2014-01-05
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
Comment
Question by:christoforatos1968
  • 3
  • 2
6 Comments
 
LVL 23

Accepted Solution

by:
NBVC earned 500 total points
ID: 39752390
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
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39752437
Please see the sample.

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

Author Comment

by:christoforatos1968
ID: 39753933
I receive an error message for this formula " =AND($E$3-Today()<60,$E$3-Today()>0) ".
Please view screenshot.
Error-message.PNG
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 23

Expert Comment

by:NBVC
ID: 39754003
It might be because of your Excel version...

Try with a semi-colon instead:

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

Expert Comment

by:Harry Lee
ID: 39754402
Christoforatos,

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

Expert Comment

by:NBVC
ID: 39754479
Hardly complicated formulas....
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question