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

[emmadgncs]

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? 

[#2]

@emmadgncs 

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

[#3]

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

[#4]

@emmadgncs 

Use

=AND(C2<>"",EDATE(TODAY(),2)>C2)

and

=AND(C2<>"",EDATE(TODAY(),6)>C2)

[#5]

@HansVogelaar

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?

[#6 · Accepted solution]

@emmadgncs 

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

See the attached, corrected version.

[#7]

@emmadgncs 

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