Highlight expiry dates based on their proximity to today's date

Copper Contributor

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? 

6 Replies



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.


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.









@Hans Vogelaar


Thank you, this is better but it isn't working perfectly for me either.


See attached.


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?

best response confirmed by emmadiagenics (Copper Contributor)


Sorry, my bad. The formulas referred to the previous row instead of the current row.

See the attached, corrected version.


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.....