Macro excel date format change

Question

I'm attempting to add a French translation to my code. I have a macro that pulls dates in the appropriate format from a text file report. Date format for text files is JUL13/2023. My macro functions perfectly, but occasionally dates appear in French. JAN stands for January, F:V for February, MAR for March, AVR for April, MAI for May, JLT for June, AO for August, SEP for September, OCT for October, NOV for November, and D:C for December. I'm looking for the best way to incorporate it into my code so that it can read any date and output only the conventional date format. This is my code:

 Sub test()
    Dim fn As String, mtch As Object, m As Object, s As Object, txt As String
    Dim i As Long
    
    fn = "C:\temp\test.txt"
    
    txt =CreateObject("scripting.filesystemobject").OpenTextFile(fn).ReadAll
     With CreateObject("vbscript.regexp")
     .Global = True
     .Pattern = "[^\n]+"
     Set mtch = .Execute(txt)
     
     i = 1
     Dim b As Long
     b = 1
     For Each m In mtch
     .Pattern = "[a-zA-Z0-9]{7}\s\s[^\s]+\s[a-zA-Z\s]*[0-9]{2}\/[0-9]{4}"
     

        
        For Each s In .Execute(m.Value)
           i = i + 1
           Cells(i, 1) = s
           b = b + 1
           Range("B" & b).Value = Right(Cells(i, 1), 10)
        
        Next
        Next
     End With
    
  
    Dim var As String   
    Dim N As Long, p As Long, j As Long
    N = Cells(Rows.Count, "B").End(xlUp).Row
    
    
    For p = 2 To N
            var = Range("B" & p).Value  
            Range("C" & p).Value = convert_date(var)
            Range("D" & p).Value = Range("C" & p) + 179
            Range("E" & p).Value = Range("C" & p) + 209
            j = j + 1
    Next p
        
End Sub


Function convert_date(date_as_string As String) As Date
   Dim mthstring As String
   mthstring = "JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC"
   convert_date = DateSerial( _
   CInt(Right(date_as_string, 4)), _
   CInt(((InStr(1, mthstring, Left(date_as_string, 3)) - 1) / 4) + 1), _
   CInt(Replace(Mid(date_as_string, 4, 2), "/", "")))
End Function


Sub testConvertDate()
    Dim var As String
    Dim N As Long, i As Long, j As Long
    N = Cells(Rows.Count, "B").End(xlUp).Row
    
    Dim m As Integer
    For i = 2 To N
            'Range("B" & i).Value = Right("A" & i, 10)
            var = Range("B" & i).Value
            
            Range("C" & i).Value = convert_date(var)
            Range("D" & i).Value = Range("C" & i) + 179
            Range("E" & i).Value = Range("C" & i) + 209
            j = j + 1
    Next i
End Sub

And here is my outcome: 

Answer 1

You must handle the string Date differently since your list of French month names includes strings of three or four characters. Please use the following modified function. Don't forget to replicate the function that only returns numbers (onlyNo):

Function convert_date(date_as_string As String) As Date
   Dim mthstring As String, strLeft As String, arrD, dayNo As Long, monthNo As Long, y As Long

   mthstring = "JANV,FEVR,MARS,AVRIL,MAI,JUIN,JUIL,AOUT,SEPT,OCT,NOV,DEC"
   arrD = Split(mthstring, ",") 'place the string in an array
   y = CLng(Split(date_as_string, "/")(1)) 'extract the year
   strLeft = Split(date_as_string, "/")(0) 'extract the left string Date split by "/"
   dayNo = onlyNo(strLeft)                 'extract the day number
   monthNo = Application.match(left(strLeft, Len(strLeft) - Len(CStr(dayNo))), arrD, 0) 'extract the month number

   convert_date = DateSerial(y, monthNo, dayNo) 'convert to Date
End Function

Private Function onlyNo(strX As String) As Long
     With CreateObject("vbscript.regexp")
       .Pattern = "[^0-9]"  'replace everything except numbers
       .Global = True
       onlyNo = CLng(.replace(strX, "")) 'remove all letters
    End With
End Function

The function must be invoked precisely as it is in your current code.

Using the below testing Sub, you can easily test it. Please execute it after uncommenting each line that has been commented:

Sub testConvert_Date()
    Dim d As String
    d = "MAI31/2022"
    'd = "JUIN20/2022"
    'd = "NOV4/2022"
    Debug.Print convert_date(d)
End Sub

I can simply change the code by adding a new argument that allows you to choose between languages if you need it to work for English days names as well.