Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 1
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 3,872
Default 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Incremental time values based upon clock in and clock out times saltnsnails Excel Discussion (Misc queries) 8 January 13th 09 09:11 PM
Formating 24hr Clock, but not as time!! SpencerMC Excel Discussion (Misc queries) 13 May 10th 08 01:42 AM
Converting format of hh:mm AM/PM to only hh:mm non 24hr in same co Mic Excel Discussion (Misc queries) 6 June 13th 07 04:08 PM
Converting 24 hr clock to GMT rgullin9 Excel Worksheet Functions 1 December 6th 05 01:46 PM
code for 24hr clock? Skullcramp Excel Programming 1 November 13th 05 01:41 AM


All times are GMT +1. The time now is 01:31 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"