• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 301
  • Last Modified:

Excel: Insert colon in military time column?

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
Pancake_Effect
Asked:
Pancake_Effect
  • 3
  • 3
1 Solution
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
Pancake_EffectAuthor Commented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
Industry Leaders: 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!

 
Pancake_EffectAuthor Commented:
Worked flawlessly, thank you so much!
0
 
Pancake_EffectAuthor Commented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now