Forum Discussion

Sharon_Dquest1's avatar
Sharon_Dquest1
Copper Contributor
Mar 11, 2021

Automatically converting MO/00/YR to the last day of the month

Hi,   I work in a lab where we use excel 365 to document out quality control. Some of the reagents we use have the expiration as MO/year. I want to know if there is a way for us to put MO/00/yr and...
excel
Sharon_Dquest1's avatar
Sharon_Dquest1
Copper Contributor
Mar 17, 2021
I tried figuring out how to do multiple columns using that code by myself but I can't get it. So in this scenario we have multiple reagents in the same worksheet that have an expiration date with the 12/00/21 format. Can I do that? Thanks,
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Mar 17, 2021

    Sharon_Dquest1 

    Let's say columns E, X and AQ contain expiration dates:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Range
    Dim m As Long
    Dim y As Long
    If Not Intersect(Range("E4:E10000,X4:X10000,AQ4:AQ10000"), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        For Each c In Intersect(Range("E4:E10000,X4:X10000,AQ4:AQ10000"), Target)
            If Mid(c.Value, 3, 4) = "/00/" Then
                m = Left(c.Value, 2)
                y = 2000 + Right(c.Value, 2)
                c.Value = DateSerial(y, m + 1, 0)
            End If
        Next c
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

    You can edit and expand this as needed.