Ideas on how to track inventory expiration dates in Excel

NOTE* Please skip until the next line highlighted in bold if you're not interested in seeing how my setup functions. Thank you!

Hello everybody,

I am currently managing a minor inventory consisting of 101 unique goods. Currently my excel sheet looks like this:

To briefly explain the sheet:

Column F = minimum stock

Column G = current stock. I have a conditional formatting filter here that highlights the stock in a red color if it is < or = the value in the corresponding F column

Column H = Add a value to the stock. So if I enter in 5 on H3, G3 will change value to 11 and the number 5 will immediately disappear from H3 allowing me to enter a new number to add to G3

Column I = Subtract a value to the stock. So if I enter in 6 on I3, G3 will change value to 0 and the number 6 will immediately disappear from I3 allowing me to enter a new number to subtract from G3

Column J = This shows me the last time that the stock in column G was updated. It also takes account for if the value in column G was updated by adding/subtracting to it from column H and I, so it's not completely dependent on manual alterations to the numbers in column G.

Finally, the macro is shown to the right, which is given by:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim r As Range, c As Range

Set r = Intersect(Target, Columns("H:I"))

If Not r Is Nothing Then

Application.EnableEvents = False

For Each c In r

With Cells(c.Row, "G")

If IsNumeric(.Value) Then

.Value = .Value + IIf(c.Column = 9, c.Value, -c.Value)

Cells(c.Row, "J").Value = Now

Cells(c.Row, "J").NumberFormat = "dd-mm-yyyy"

c.ClearContents

End If

End With

Next c

Application.EnableEvents = True

End If

End Sub

Now, to my actual question:

Currently, I receive my stock at random intervals ranging anywhere from 7-60 days. Whenever this happens I naturally have to update my stock, which works fine. However, I have some trouble with my expiration dates.

Every batch of items I receive has the same expiration date (meaning that if I order 50 items of "Material XYZ 527" all 50 of these similar materials will have the same expiration date (for example 10-12-2020)). However some times I may still have 1 or 2 of these laying around when I receive a new batch, thus meaning I can have the same item in one shelf but with different expiration dates.

I want to somehow keep track of all these expiration dates through a FEFO (first expired, first out) concept, so I can check and see how many items for each material I have expiring on this and that date.

I would love for this to somehow be compatible with my current system.

Does anybody have any experience with this or good ideas? I would truly appreciate any inputs that I can get! If anybody has some nice videos, guides, links or anything that might help, please do not hesitate to drop those in the comments either :)

Thank you so much for your time everybody! It is truly appreciated :)

Best regards,

David