Forum Discussion
Highlight expiry dates based on their proximity to today's date
- Oct 12, 2023
Sorry, my bad. The formulas referred to the previous row instead of the current row.
See the attached, corrected version.
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.
- emmadgncsOct 12, 2023Copper Contributor
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?
- HansVogelaarOct 12, 2023MVP
Sorry, my bad. The formulas referred to the previous row instead of the current row.
See the attached, corrected version.
- mathetesOct 12, 2023Gold Contributor
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.....