Macro "Time formatting" help

[Guest]

I've got a column of "Time" information that doesn't have a colon separating
the hours from the minutes. I'm looking for a macro to:

move 2 places to the left
insert ":"

An example of the data are
0957
1412
1952

and I want
09:57
14:12
19:52

The data begin at B2.

Thanks,
Norm

 

[JE McGimpsey]

Sure you need a macro? You could try this formula:

=--TEXT(A1,"00\:00")

Or, with a macro:

Public Sub SelectionToTime()
Dim rCell As Range
For Each rCell In Selection.Cells
With rCell
If .Text Like "####" Then
.Value = TimeValue(Format(.Text, "00\:00"))
.NumberFormat = "[hh]:mm"
End If
End With
Next rCell
End Sub

 

[Guest]

JE,

You are right, I don't need a macro. Worked wonderfully! Where are all
these formulas hidden?! I am always amazed (and appreciative) at how quickly
and effectively questions are responded to.

Thanks,
Norm

Sure you need a macro? You could try this formula:

=--TEXT(A1,"00\:00")

Or, with a macro:

Public Sub SelectionToTime()
Dim rCell As Range
For Each rCell In Selection.Cells
With rCell
If .Text Like "####" Then
.Value = TimeValue(Format(.Text, "00\:00"))
.NumberFormat = "[hh]:mm"
End If
End With
Next rCell
End Sub

I've got a column of "Time" information that doesn't have a colon separating
the hours from the minutes. I'm looking for a macro to:

move 2 places to the left
insert ":"

An example of the data are
0957
1412
1952

and I want
09:57
14:12
19:52

The data begin at B2.

Thanks,
Norm