Conditional Formatting or Expiration Dates [SOLVED]
- Thread starter montalvom
- Start date
create 2 conditional formats using a formula. basically the formula will take todays date and subtract the date given (the exp. date). one rule will see if it is less than 90 days (3months) and format the cell with yellow fill. the second one will see if the date is less that or equal to 0 and format the cell with red fill.
(F1 will need to be replaced with whatever cell ref contains your first Date)
=(TODAY()-F1)<90
set formatting to be yellow fill and whatever else
=(TODAY()-F1)<0
set formatting to be red fill and whatever else
(F1 will need to be replaced with whatever cell ref contains your first Date)
=(TODAY()-F1)<90
set formatting to be yellow fill and whatever else
=(TODAY()-F1)<0
set formatting to be red fill and whatever else
SirJB7
Excel Rōnin
Hi, montalvom!
First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.
As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).
Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.
Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.
And about questions in general...
If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.
And about this question in particular...
Assuming that you have data in columns A:E with date in D column, try this:
a) Select A:E columns.
b) Go to Start tab, Styles group, Conditional Formatting icon.
c) New Rule, Use Formula.
d) Enter this formula:
=Y(FILA()>1;$D1>0;$D1-HOY()<=90) -----> in english: =AND(ROW()>1,$D1>0,$D1-TODAY()<=$D1)
e) Format, Fill, Yellow, Accept.
f) Accept.
Regards!
First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.
As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).
Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.
Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.
And about questions in general...
If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.
And about this question in particular...
Assuming that you have data in columns A:E with date in D column, try this:
a) Select A:E columns.
b) Go to Start tab, Styles group, Conditional Formatting icon.
c) New Rule, Use Formula.
d) Enter this formula:
=Y(FILA()>1;$D1>0;$D1-HOY()<=90) -----> in english: =AND(ROW()>1,$D1>0,$D1-TODAY()<=$D1)
e) Format, Fill, Yellow, Accept.
f) Accept.
Regards!
Thanks for the great suggestions. I created some codes that got the result that I wanted before I received your replies but am having an issue that blank cells are now being conditionally formatted as well. Code I wrote was:
=TODAY()>A1 FORMATTED AS RED
=TODAY()+30>A1 FORMATTTED AS YELLOW
=TODAY()+30<A1 FORMATTED AS GREEN
not sure why all the empty cells are defaulting to red. Any help is appreciated.
=TODAY()>A1 FORMATTED AS RED
=TODAY()+30>A1 FORMATTTED AS YELLOW
=TODAY()+30<A1 FORMATTED AS GREEN
not sure why all the empty cells are defaulting to red. Any help is appreciated.