Need UDF to return time format from string input

**Andrew_Harris** · 10-25-2012, 09:09 PM

I have a lot of login logout data that that displays time as, for example, "1:37:22PM" as a string. I'm trying to make a UDF that inputs time in that format (I've attached a workbook with a small sample dump of the times) and outputs the correct time format so that I can use comparison operators. Unfortunately, I seem to be incredibly bad at parsing this and keep messing up. Any tips or help would be incredibly appreciated,

Thanks!

testbook.xlsx

Edit: Apparently, I also am bad at attachements.

**jindon** · 10-25-2012, 09:30 PM

try this

Sub test()
    Dim r As Range, temp As Date
    Application.ScreenUpdating = False
    On Error Resume Next
    With Range("a1").CurrentRegion
        For Each r In .Columns("a:b").Cells
            Err.Clear
            temp = CDate(r.Value)
            If Err = 0 Then
                r.NumberFormat = "h:mm:ss AM/PM"
                r.Value = temp
            End If
        Next
    End With
    Application.ScreenUpdating = True
End Sub