Converting data to 24hr clock
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 |
Converting data to 24hr clock
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 |
All times are GMT +1. The time now is 07:41 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com