Help with converting date and time from text

Occasional Contributor

Trying desperately to run a report however I need to convert text to date and time. 

1 Reply

@62Mojo 

Run this macro:

 

Sub Text2Date()
    Dim m As Long
    Dim c As Long
    Application.ScreenUpdating = False
    m = Range("A1").End(xlDown).Row
    With Range("B2:E" & m)
        .Replace What:="Mon, ", Replacement:="", LookAt:=xlPart
        .Replace What:="Tue, ", Replacement:="", LookAt:=xlPart
        .Replace What:="Wed, ", Replacement:="", LookAt:=xlPart
        .Replace What:="Thu, ", Replacement:="", LookAt:=xlPart
        .Replace What:="Fri, ", Replacement:="", LookAt:=xlPart
        .Replace What:="Sat, ", Replacement:="", LookAt:=xlPart
        .Replace What:="Sun, ", Replacement:="", LookAt:=xlPart
        .Replace What:="st", Replacement:="", LookAt:=xlPart
        .Replace What:="nd", Replacement:="", LookAt:=xlPart
        .Replace What:="rd", Replacement:="", LookAt:=xlPart
        .Replace What:="th", Replacement:="", LookAt:=xlPart
        .Replace What:="am", Replacement:=" am", LookAt:=xlPart
        .Replace What:="pm", Replacement:=" pm", LookAt:=xlPart
        Range("F1").Copy
        .PasteSpecial Operation:=xlPasteSpecialOperationAdd
        Application.CutCopyMode = False
        .NumberFormat = "m/d/yyyy h:mm AM/PM"
    End With
    Application.ScreenUpdating = True
End Sub