View original (French)

Excel, can it manage expiration dates?

Excel Beginner -  
 Martial -
Hello everyone,

so here I am gradually moving to the virtualization of my documents and I have boxes of products for which I need to manage expiration dates. These boxes have barcodes.

The license of my software that manages my stocks does not handle expiration dates and since I keep a manual notebook with the boxes to be removed based on the dates, I would like to know if I could have a solution in Excel that would notify me by changing a cell, a color, or something based on the expiration date mentioned?

A sort of countdown... and if possible managing the information determined by my barcode (that's optional).

Hoping not to bother you too much with this question and thanking you very much.

Excel Beginner

Configuration: Windows XP / Firefox 31.0

2 answers

  1.
    PHILOU10120 Posted messages 6463 Registration date   Status Contributor Last intervention   835
     
    Hello

    Excel can help manage expiration dates; you just need to create a formula that determines the expiration date based on the manufacturing date

    =IF(TODAY()>C7+365,"expired",IF(TODAY()>C7+335,"less than 30 days left",""))

    In C7 is the reference date; in this example, the product expires after 365 days, and an alert notifies you 30 days before expiration
    Additionally, you can use conditional formatting to change the cell to green, then orange, and finally red

    --
    Practice makes perfect. - It is when pushed against the wall that one sees the mason - we always learn from our mistakes.
    7
    1. Martial
       
      Hello, I am an Excel enthusiast and I admit that you really help us, indeed by solving our problems but also by allowing us to learn.
      A big thank you to you.
      0
  2. Maurice
     
    Bonjour
    you can also make a macro

    Ex:
    Sub TestDate()
    ' 25 = Column "Y"
    Nlig = Cells(Rows.Count, 25).End(xlUp).Row
    For L = 2 To Nlig
    If Cells(L, 25).Value < DateValue(Now) Then
    Cells(L, 25).Interior.ColorIndex = 3
    Else
    Cells(L, 25).Interior.ColorIndex = xlNone
    End If
    Next
    End Sub

    See you
    Maurice
    0