Solved

Excel: Insert colon in military time column?

Posted on 2012-03-12
6
284 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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
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!
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

776 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