Excel change date format-CodePudding

I have extracted with a macro report that gives me the following date format: JUL13/2023 Is there a way or formula that can transfer it to a regular date format? I need to track how many days passed from this day to a certain day.

CodePudding user response：

Try this test:

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 test()
    Debug.Print convert_date("JUL13/2023")
    Debug.Print convert_date("JUL8/2023")
End Sub

Sub test2()
    Dim var As String
    var = Range("A1").Value
    Range("B1").Value = convert_date(var)
End Sub

CodePudding user response：

This is a formula solution:

=DATEVALUE(MID(A1,MIN(IFERROR(SEARCH({0,1,2,3,4,5,6,7,8,9},A2),"")),FIND("/",A1,1)-MIN(IFERROR(SEARCH({0,1,2,3,4,5,6,7,8,9},A2),"")))&"/"&LEFT(A1,MIN(IFERROR(SEARCH({0,1,2,3,4,5,6,7,8,9},A1),""))-1)&"/"&RIGHT(A1,4))