Forum Discussion
Highlight expiry dates based on their proximity to today's date
Sorry, my bad. The formulas referred to the previous row instead of the current row.
See the attached, corrected version.
=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.
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.
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?
Sorry, my bad. The formulas referred to the previous row instead of the current row.
See the attached, corrected version.