Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Excel: Insert colon in military time column?

Posted on 2012-03-12
6
285 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

856 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