Oct 12 2023 03:43 AM
Oct 12 2023 03:43 AM
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?
Oct 12 2023 04:05 AM
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.
Oct 12 2023 04:24 AM
Oct 12 2023 05:15 AM
Oct 12 2023 06:49 AM
Thank you, this is better but it isn't working perfectly for me either.
The formula is highlighting a range of 3 consecutive months for products that expire in 2 or less months time. Preferably 01/01/2024 shouldn't be highlighting in red until we hit the month of November.
I am guessing that this has something to do with the day. Is there a way to display the date that doesn't include the day? Preferably mm/yy or mm/yyyy? Then perhaps excel would recognise that the month listed is also included in the 2 months?
Because our products expire on the last day of the month, not the first. But I don't want to create more work by having to check how many days are in said month when entering the expiry date.
I would also need 01/10/2023 to remain highlighted in red, otherwise it could give the impression that it has 6 months + expiry. Is this possible?
Oct 12 2023 07:00 AMSolution
Sorry, my bad. The formulas referred to the previous row instead of the current row.
See the attached, corrected version.
Oct 12 2023 07:15 AM
It's not clear whether you're tracking stock (as in medical supplies; an inference from your user name) or stock as in stock market stuff. But, I thought I'd add to what @Hans Vogelaar has given by just posting the image below, which is a set of rules I've written that compare today's date with a set of expiration dates of stock options, and highlight with green if that expiration is within the current month, other colors if next or the one after next.....