Hi guys I'd appreciate some help.

I have a spreadsheet containing the time complaints were received. About 9000 entries are in the 24 hr format (e.g. 12:34), but about 1000 are not and are recorded as single or double digits without the colon (e.g 12 instead of 12:00).
How can I covert all numbers to the same 24hr format?

Thanks jo

Hi Jo,

Am Mon, 18 May 2015 20:51:26 +0100 schrieb josmles:

I have a spreadsheet containing the time complaints were received. About
9000 entries are in the 24 hr format (e.g. 12:34), but about 1000 are
not and are recorded as single or double digits without the colon (e.g
12 instead of 12:00).
How can I covert all numbers to the same 24hr format?

try:

Sub ConvertTimes()
Dim LRow As Long
Dim rngC As Range

With ActiveSheet
LRow = .Cells(Rows.Count, "A").End(xlUp).Row
For Each rngC In .Range("A1:A" & LRow)
If rngC 0 Then
Select Case Len(rngC)
Case Is <= 2
rngC = TimeSerial(rngC, 0, 0)
Case 3
rngC = TimeSerial(Left(rngC, 1), Mid(rngC, 2), 0)
Case 4
rngC = TimeSerial(Left(rngC, 2), Right(rngC, 2), 0)
End Select
End If
Next
.Range("A1:A" & LRow).NumberFormat = "h:mm"
End With
End Sub

Modify the column to your time column


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional