Modify Excel Macro to Format Time Correctly

[Evil8]

I have the code below in my Excel Macro to convert time from values like 11am (or 2pm) to 11:00:00 AM (or 2:00:00 PM). My end users want this data on a webpage sorted by time and these values don't work for that. I had issues with sorting these times on a webpage MySQL query and found that things work better if I use military time values in the tables.

For Each r In Range("C2:C" & Cells(Rows.Count, "C").End(x1Up).Row)
    a = Split(r.Value, "am")

    If IsNumeric(a(0)) Then
        r.Value = TimeSerial(a(i), 0, 0)
    Else
        a = Split(r.Value, "pm")
        If a(0) = "12" Then a(0) = "0"
        r.Value = TimeSerial(a(0) + 12, 0, 0)
   End If

Next

So times for this spreadsheet should now be 11:00:00 (or 14:00:00) so they sort properly. I've got them converting back to the proper format using the webpage php.

Thank you all for your help.

 

[SkipVought]

hi,

Sub test()
    Dim x As String
    
    x = "11am"
    
    MsgBox TimeValue(x)
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]