Forum Discussion

emmadgncs's avatar
emmadgncs
Copper Contributor
Oct 12, 2023
Solved

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

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 t...
excel
Formulas and Functions
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Oct 12, 2023

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.

  • emmadgncs's avatar
    emmadgncs
    Copper Contributor
    Oct 12, 2023
    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.
    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Oct 12, 2023

      emmadgncs 

      Use

       

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

       

      and

       

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

      • emmadgncs's avatar
        emmadgncs
        Copper Contributor
        Oct 12, 2023

        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?

Resources