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

%3CLINGO-SUB%20id%3D%22lingo-sub-2202671%22%20slang%3D%22en-US%22%3EAutomatically%20converting%20MO%2F00%2FYR%20to%20the%20last%20day%20of%20the%20month%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2202671%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20work%20in%20a%20lab%20where%20we%20use%20excel%20365%20to%20document%20out%20quality%20control.%20Some%20of%20the%20reagents%20we%20use%20have%20the%20expiration%20as%20MO%2Fyear.%20I%20want%20to%20know%20if%20there%20is%20a%20way%20for%20us%20to%20put%20MO%2F00%2Fyr%20and%20have%20it%20automatically%20change%20to%20the%20last%20day%20in%20that%20month.%20I%20have%20the%20excel%20sheets%20set%20up%20to%20flag%20expiration%20dates%20that%20are%20before%20the%20date%20the%20test%20is%20performed.%20What%20I've%20found%20is%20that%20the%20cell%20doesn't%20recognize%20Mo%2F00%2FYR%20as%20a%20particular%20date%20so%20it%20doesn't%20flag.%20For%20example%2C%20a%20reagent%20expiration%20date%20is%2002%2F00%2F21%20and%20we%20performed%20the%20test%20today%2C%20it%20won't%20flag%20it%20as%20past%20expiration.%20Any%20help%20is%20appreciated.%20Thanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2202671%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Contributor

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 have it automatically change to the last day in that month. I have the excel sheets set up to flag expiration dates that are before the date the test is performed. What I've found is that the cell doesn't recognize Mo/00/YR as a particular date so it doesn't flag. For example, a reagent expiration date is 02/00/21 and we performed the test today, it won't flag it as past expiration. Any help is appreciated. Thanks!

8 Replies

@Sharon_Dquest1 

Right-click the sheet tab.

Select 'View Code' from the context menu.

Copy the following code into the worksheet module:

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"), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        For Each c In Intersect(Range("E4:E10000"), 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

Switch back to Excel.

Save the workbook as a macro-enabled workbook.

Make sure that you allow macros when you open it.

That works. Thanks so much!!
Well, it was working...

I was having to change some the sheets because some them have the expiration column in D instead of E. I'm not sure what I was doing wrong but now it is working.  Thanks for the help! @Hans Vogelaar 

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,

@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.

@Sharon_Dquest1 although @Hans Vogelaar gave you a nice macro why not just do it in the sheet?  I'm going to assume in some cases you have MM/DD/YYYY expirations (middle of the month) and can't apply a EOMONTH() to the dates and that is why you are entering MM/00/YYYY.  But if you have to train someone to always do that, why not just train them to enter MM+1/YYYY?  If your quality system says a reagent that expired 04/2021 is good through the month of 04/2021 then just enter 05/2021 into your system.  Either way you have to train the person entering the data and you will have human error in either case.