Hi,

I am trying to do a function that extracts a date from the end of a string.

This date may have multiple formats
d-m
dd-m
d/m
d/m/yy
mmmyy
etc

I have tried the below but does not work.

Option Explicit

Function FindDate(StringWithDate As String) As Integer
  
    Dim strInput As String
    Dim LenStr As Integer
    Dim i As Integer
    
    strInput = StringWithDate
    
    strInput = Replace(strInput, " ", "")
    strInput = Replace(strInput, ".", "-")
    strInput = Replace(strInput, "/", "-")
    
    strInput = Replace(strInput, "Jan", "-01-")
    strInput = Replace(strInput, "Feb", "-02-")
    strInput = Replace(strInput, "Mar", "-03-")
    strInput = Replace(strInput, "Apr", "-04-")
    strInput = Replace(strInput, "May", "-05-")
    strInput = Replace(strInput, "Jun", "-06-")
    strInput = Replace(strInput, "Jul", "-07-")
    strInput = Replace(strInput, "Aug", "-08-")
    strInput = Replace(strInput, "Sep", "-09-")
    strInput = Replace(strInput, "Oct", "-10-")
    strInput = Replace(strInput, "Nov", "-11-")
    strInput = Replace(strInput, "Dec", "-12-")
    
    strInput = Replace(strInput, "--", "-")
    
    If Right(strInput, 1) = "-" Then strInput = strInput & Format(Now, "yyyy")
    
    LenStr = Len(strInput) + 1
    
    FindDate = 0

    For i = 1 To LenStr
        strInput = Right(strInput, i)
    
        If IsDate(strInput) Then
            FindDate = Val(Right(strInput, i))
            'FindDate = strInput
            GoTo FinishSub
        End If
    Next i

FinishSub:
End Function
Can someone help?

Thanks and regards,

SAI