Macro excel date format change

0 votes

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: enter image description here

Feb 14, 2023 in Others by narikkadan
• 63,420 points
226 views

1 answer to this question.

0 votes

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.

answered Feb 14, 2023 by Kithuzzz
• 38,010 points

Related Questions In Others

0 votes
1 answer

Change date format of cell in excel from dd.mm.yyyy to yyy/mm/dd ( excel version 2013 )

Hello :)   Excel’s Format Cells function can quickly ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
1,288 views
0 votes
1 answer

How to change two different date format into single date format in excel

With data in A2, in B2 enter: =IF(ISNUMBER(A2),A2,DATE(LEFT(A2,4),MID(A2,6,2),RIGHT(A2,2))) and apply your format ...READ MORE

answered Dec 24, 2022 in Others by narikkadan
• 63,420 points
839 views
0 votes
1 answer

Is there any way to change input type="date" format?

To answer your question, it is impossible ...READ MORE

answered Feb 18, 2022 in Others by Aditya
• 7,680 points
14,276 views
0 votes
1 answer

Convert column in excel date format (DDDDD.tttt) to datetime using pandas

Given # s = df['date'] s 0 ...READ MORE

answered Oct 2, 2022 in Others by narikkadan
• 63,420 points
2,747 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
876 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,183 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
480 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
731 views
0 votes
1 answer

How to change date format from 201904 to Apr-19 in excel

This 201904 is recognised as a number ...READ MORE

answered Mar 31, 2023 in Others by Kithuzzz
• 38,010 points
225 views
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP