Solved

Excel: Insert colon in military time column?

Posted on 2012-03-12
6
286 Views
Last Modified: 2012-03-12
We have a excel work sheet, there is a very long column that states the time for an event. We need the time in the format below:


Original
hhmm

Needed Change
hh:mm


This is my original code below for other changes, if it could be added for range G:G for example that would be great!

Thanks!

Sub replaceText()
Dim c As Range, rg As Range
Application.ScreenUpdating = False
Set rg = Intersect(Range("A:A"), ActiveSheet.UsedRange)
For Each c In rg.Cells          'Only search column L
    Select Case c.Value
        Case "B"              	'Old value
            c.Value = 3         'New value
            
        Case "BAP"              'Old value
            c.Value = 3         'New value
            
        Case "BB"               'Old value
            c.Value = 3    	'New value
            
       

        Case Else               'Do nothing
    End Select
Next

Application.ScreenUpdating = False
Set rg = Intersect(Range("C:C"), ActiveSheet.UsedRange)
For Each c In rg.Cells          'Only search column L
    Select Case c.Value
        Case "M"              'Old value
            c.Value = "Male"         'New value
            
        Case "F"              'Old value
            c.Value = "Female"         'New value
            
            
        Case Else               'Do nothing
    End Select
Next
Set rg = Intersect(Range("D:D"), ActiveSheet.UsedRange)
rg.NumberFormat = "@"
For Each c In rg.Cells          'Only search column D (ARADMDT)
    If c <> "" Then c.Value = Mid(c.Value, 5, 2) & Right(c.Value, 2) & Left(c.Value, 4)       'Change date from yyyymmdd to ddmmyyyy
Next
End Sub

Open in new window

0
Comment
Question by:Pancake_Effect
  • 3
  • 3
6 Comments
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 37712203
Hello,

are the time values text or numbers? How do times between midnight and 1 am show? With leading zeroes?

It might be useful to see a few samples, including values just after midnight.

cheers, teylyn
0
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 37712351
I did a cut and paste from the original document. (Attached) This is what an example of what their work currently is.

The sheet of changes states it needs to be changed to hh:mm. That's all it said that is needing to be changed. So I'm assuming just a colon is needed.
times.xlsx
0
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst earned 500 total points
ID: 37712449
Hello,

add this before the end of the sub:

Dim strTime As String
Set rg = Intersect(Range("G:G"), ActiveSheet.UsedRange)
For Each c In rg.Cells
    If IsNumeric(c) Then
        Select Case Len(c)
            Case 1
                strTime = "00:0" & c
            Case 2
                strTime = "00:" & c
            Case 3
                strTime = Left(c, 1) & ":" & Right(c, 2)
            Case 4
                strTime = Left(c, 2) & ":" & Right(c, 2)
        End Select
        c = strTime
    End If
Next c

Open in new window


This will work even for values like 6 resulting in 00:06 and 11 resulting in 00:11

cheers, teylyn
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Author Closing Comment

by:Pancake_Effect
ID: 37712686
Worked flawlessly, thank you so much!
0
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 37712716
Say I have one last question I forgot to ask before I marked this question as answered, is there a way to add a zero for example 06:00 instead of 6:00? Thanks!
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 37712860
The cells are actually converted to time values. How they display can be specified with the cell format. So, you may want to use the custom format

hh:mm

after running the macro.

cheers, teylyn
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question