CODEWITHSUNDEEP
excelvba
Ana
Ana

Reputation: 119

Excel change date format

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.

Upvotes: 2

Views: 120

Answers (4)

5202456
5202456

Reputation: 962

This is a formula solution:

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

Upvotes: 0

CLR
CLR

Reputation: 12289

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

Sub test3()
    Dim start_row As Long, end_row As Long, sht As Worksheet
    
    Set sht = ActiveSheet
    start_row = 2
    end_row = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
    
    For Each cel In sht.Range("B" & start_row & ":B" & end_row).Cells
        cel.Value = convert_date(cel.Offset(0, -1).Value)
    Next
    
End Sub

Upvotes: 4

Jim Becker
Jim Becker

Reputation: 209

VBA solution:

TextDate = "JUL13/2023"

DateDate = CDate(Left(TextDate, 3) & " " & Mid(TextDate, 4, 2) & " " & Right(TextDate, 4))

Upvotes: 0

Jim Becker
Jim Becker

Reputation: 209

Insert a helper column. Format it with your preferred date format.

This formula takes advantage of Excel's implicit date conversion:

=(MID(A2,4,2)&LEFT(A2,3)&RIGHT(A2,4))*1

Upvotes: 0

Related Questions