Apr 16 2021 06:07 AM
Trying desperately to run a report however I need to convert text to date and time.
Apr 16 2021 07:31 AM
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