• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 311
  • 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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

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