Sorry, my example says "9pm" & "45pm" where I meant "am".

Mike F
"Mike Fogleman" wrote in message
m...
That's great! However I have one more question. Will you have to deal with
times between midnight and 1:00 AM? I have found in my experience that the
leading 0's are truncated off. 9 minutes after midnight may import as
"9pm", 45 minutes would be "45pm", etc. The code I gave you only works on
string lengths of 5 or greater ("100am"). More code is needed to check if
the string length is <5 and deal with the possibility that it is 3 or 4
characters in length.
If your situation fits this scenario, then post back with an example of
those times.

Mike F
"Robert H" wrote in message
ups.com...
I used:

Sub FixTime()
Dim timeCell As Range

For Each timeCell In Selection
If Right(timeCell, 2) = "pm" Or Right(timeCell, 2) = "am" Then
timeCell.Value = Left(timeCell, Len(timeCell) - 4) & ":" & _
Mid(timeCell, Len(timeCell) - 3, 2) & " " & Right(timeCell, 2)
timeCell.NumberFormat = "hh:mm"
End If
Next
End Sub

and it worked perfect. Thaks Mike and all for posting.
V/R
Robert