Forum Discussion
Highlight expiry dates based on their proximity to today's date
I would like the attached stock list to fill the expiry date cells in a colour to highlight when a product only has 6 months shelf life remaining and 2 months shelf life remaining.
For products that expire in 6 months or less - highlight expiry date cell in orange.
For products that expire in 2 months or less - highlight expiry date cell in red.
I need the cell to continue highlighting in red if the product has expired. So, if the no. of months is less than 0.
I have created column G which calculates the number of months between the expiry date and H1 (Today's date). This may not be needed.
I do not want the heading rows (2,14,20) or the blank rows (13,19) to be affected.
Is anyone able to show me how to create formula to make this happen?
Sorry, my bad. The formulas referred to the previous row instead of the current row.
See the attached, corrected version.
6 Replies
=NOT(ISBLANK(C2))*(TODAY()+60>C2)This is the rule for conditional formatting for red (For products that expire in 2 months or less). The other rule is similar. The priority of the rules is important. The rule for red is above the rule for orange.
- OliverScheurich
Thank you.
Is there anyway to do this by calculating months instead of days. Obviously some months have more or less than 30 days and so I don't want this to make it inaccurate.
All of my products expire on the last day of the month. I just entered the 1st of the month for ease. I would prefer a date format that was only mm/yy but i couldn't figure out how to do this.
