I would like to use the dates in a certain column of an inventory spreadsheet to determine if a product's warranty has expired. I would like to mark the row in one color if the expiration date has already passed, another color if the date is within 1 year of the current date, and leave other rows as is. Can someone please tell me how I can do that, if possible? Thank you.
Welcome to the forum.
You'll need two CF rules.
There are instructions at the top of the page explaining how to attach your sample workbook.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
I've added an attachment, thank you for the reply.
As AliGW states two conditional formatting rules will be needed.
Formula for red fill (I suggest white font): =$F2< TODAY()
Formula for yellow fill: =$F2< EDATE(TODAY(),12)
Note that both rules have an applies to of: =$A$2:$H$5
Note that the order of the rules is important i.e. red rule is listed first.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
That works, thank you. Is there a way to stop the rule from marking rows without dates as red? Other than to clear rules from those rows?
This:
=AND($F2<>"",$F2< TODAY())
=AND($F2<>"",$F2< EDATE(TODAY(),12))
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.
Perfect, thank you.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks