Mar 11 2021 07:16 AM
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!
Mar 11 2021 07:32 AM
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.
Mar 11 2021 09:37 AM
Mar 11 2021 10:10 AM
Mar 11 2021 11:21 AM
Not anymore?
Mar 17 2021 05:31 AM
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
Mar 17 2021 10:48 AM
Mar 17 2021 10:59 AM
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.
Mar 17 2021 11:50 AM
@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.