Multiple Date Formats

Dear All,

I receive data in different date formats( dd/mm/yyyy hh:mm:ss, dd-mm-yyyy hh:mm:ss.....etc date, text mixed formats). I want final output should be in mm/dd/yyyy hh:mm:ss

Below macro (not written by me) only does part of the job. I have attached the excel file. Column B highlighted in red is not converted as expected. I want date to be converted as in column C (i.e: mm/dd/yyyy hh:mm:ss)

Sub a1105914a()
Dim va, x
Dim i As Long, c As Range, z As String
With Range("A5", Cells(Rows.Count, "A").End(xlUp))
va = .Value
For i = 1 To UBound(va, 1)
    x = va(i, 1)
    z = CStr(Format(x, "Short Time"))
    If Left(x, 2) < 13 Then
    va(i, 1) = Format(DateSerial(Year(x), Day(x), Month(x)), "dd-mm-yy") & " " & z
    Else
    va(i, 1) = CDate(x)
    End If
Next
.Value = va
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
.HorizontalAlignment = xlRight
End With
End Sub

PC time format is mm/dd/yyyy

Please any one help me,

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between [code] [/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. As you are new here, I have done it for you this time.)

Administrative Note:

Members will tailor the solutions they offer to the version (NOT release number) of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.