I have imported data into excel and one column contains the military time.
It is displayed as below. How can I insert a ":" for the entire column in
the correct place. I have not been able to do it through Format-Cells menu.

160526 16:05:26
90928 9:09:28
92155 9:21:55
110101 11:01:01

Thanks,
JS

Did you try using a Custom format like this..

#0\:00\:00

Rick


"JS" wrote in message
...
I have imported data into excel and one column contains the military time.
It is displayed as below. How can I insert a ":" for the entire column in
the correct place. I have not been able to do it through Format-Cells
menu.

160526 16:05:26
90928 9:09:28
92155 9:21:55
110101 11:01:01

Thanks,
JS

Try this (assuming your value is in cell C18):

=IF(LEN(C18)=5,TIME(LEFT(C18,1),LEFT(RIGHT(C18,4), 2),RIGHT(C18,2)),TIME(LEFT(C18,2),LEFT(RIGHT(C18,4 ),2),RIGHT(C18,2)))

HTH


"JS" wrote:

I have imported data into excel and one column contains the military time.
It is displayed as below. How can I insert a ":" for the entire column in
the correct place. I have not been able to do it through Format-Cells menu.

160526 16:05:26
90928 9:09:28
92155 9:21:55
110101 11:01:01

Thanks,
JS

One way:

if the data starts in cell A1, put this formula in cell B1 (or any free
cell)

=TIME(IF(LEN(A1)=6,LEFT(A1,2),LEFT(A1,1)),IF(LEN(A 1)=6,MID(A1,3,2),MID(A1,2,2)),RIGHT(A1,2))

format as h:mm:ss and drag down

Regards

Trevor


"JS" wrote in message
...
I have imported data into excel and one column contains the military time.
It is displayed as below. How can I insert a ":" for the entire column in
the correct place. I have not been able to do it through Format-Cells
menu.

160526 16:05:26
90928 9:09:28
92155 9:21:55
110101 11:01:01

Thanks,
JS

Rick

am I right in thinking that this looks OK but it's not actually a time value
?

Regards

Trevor


"Rick Rothstein (MVP - VB)" wrote in
message ...
Did you try using a Custom format like this..

#0\:00\:00

Rick


"JS" wrote in message
...
I have imported data into excel and one column contains the military time.
It is displayed as below. How can I insert a ":" for the entire column
in
the correct place. I have not been able to do it through Format-Cells
menu.

160526 16:05:26
90928 9:09:28
92155 9:21:55
110101 11:01:01

Thanks,
JS

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

Format cells as: hh:mm:ss


"JS" wrote:

I have imported data into excel and one column contains the military time.
It is displayed as below. How can I insert a ":" for the entire column in
the correct place. I have not been able to do it through Format-Cells menu.

160526 16:05:26
90928 9:09:28
92155 9:21:55
110101 11:01:01

Thanks,
JS

Yep, you are right... it is not a date. I originally came up with something
close to what 'Teethless mama' posted (except using the pattern string I
posted), but realized the OP wanted to change his data in place, not in
another column. So, when the same pattern string I used in TEXT worked in
custom format, I made the sloppy guess that all was well.

Rick


"Trevor Shuttleworth" wrote in message
...
Rick

am I right in thinking that this looks OK but it's not actually a time
value ?

Regards

Trevor


"Rick Rothstein (MVP - VB)" wrote in
message ...
Did you try using a Custom format like this..

#0\:00\:00

Rick


"JS" wrote in message
...
I have imported data into excel and one column contains the military
time.
It is displayed as below. How can I insert a ":" for the entire column
in
the correct place. I have not been able to do it through Format-Cells
menu.

160526 16:05:26
90928 9:09:28
92155 9:21:55
110101 11:01:01

Thanks,
JS

I have imported data into excel and one column contains the military time.
It is displayed as below. How can I insert a ":" for the entire column in
the correct place. I have not been able to do it through Format-Cells
menu.

160526 16:05:26
90928 9:09:28
92155 9:21:55
110101 11:01:01

Okay, I think you are asking for a way to change the data in the same column
it is being placed in. You won't be able to do that with spreadsheet
formulas; so, if you are up for a macro solution, put the following code in
the code window for the sheet you want it to operate on...

Sub FixColumnTime()
Dim R As Range
Dim C As Range
Set R = Range("C:C")
Application.EnableEvents = False
For Each C In R
If C.Text = "" Then
Exit For
ElseIf IsNumeric(C.Text) Then
C.Value = TimeSerial(Int(C.Value / 10000), _
Int((C.Value Mod 10000) / 100), _
C.Value Mod 100)
End If
Next
Application.EnableEvents = True
End Sub

Here, I have set the subroutine to operate on Column "C"... change the Set
statement above to reflect the column you actually want to change. As
constructed, the code will execute down the column, changing pure numbers
only (so you can run the macros over already converted value without
affecting them), and will stop executing at the first empty cell it
encounters. To execute this code (once you have pasted it into your
worksheet), select Tools/Macro/Macros from the spreadsheet's menu (or press
Alt+F8), select the FixColumnTime macro and click the Run button.

Rick

Rick

Rick,

Thank you, your first post resolved my issue.

"Rick Rothstein (MVP - VB)" wrote:

I have imported data into excel and one column contains the military time.
It is displayed as below. How can I insert a ":" for the entire column in
the correct place. I have not been able to do it through Format-Cells
menu.

160526 16:05:26
90928 9:09:28
92155 9:21:55
110101 11:01:01

Okay, I think you are asking for a way to change the data in the same column
it is being placed in. You won't be able to do that with spreadsheet
formulas; so, if you are up for a macro solution, put the following code in
the code window for the sheet you want it to operate on...

Sub FixColumnTime()
Dim R As Range
Dim C As Range
Set R = Range("C:C")
Application.EnableEvents = False
For Each C In R
If C.Text = "" Then
Exit For
ElseIf IsNumeric(C.Text) Then
C.Value = TimeSerial(Int(C.Value / 10000), _
Int((C.Value Mod 10000) / 100), _
C.Value Mod 100)
End If
Next
Application.EnableEvents = True
End Sub

Here, I have set the subroutine to operate on Column "C"... change the Set
statement above to reflect the column you actually want to change. As
constructed, the code will execute down the column, changing pure numbers
only (so you can run the macros over already converted value without
affecting them), and will stop executing at the first empty cell it
encounters. To execute this code (once you have pasted it into your
worksheet), select Tools/Macro/Macros from the spreadsheet's menu (or press
Alt+F8), select the FixColumnTime macro and click the Run button.

Rick

Rick

You did note the follow up discussion where we discussed the fact that my
first post did not produce a true "time" value, it only made a your number
"look" like a time value (as an example, put this in that column to see what
I mean... 987654).

Rick


"JS" wrote in message
...
Rick,

Thank you, your first post resolved my issue.

"Rick Rothstein (MVP - VB)" wrote:

I have imported data into excel and one column contains the military
time.
It is displayed as below. How can I insert a ":" for the entire column
in
the correct place. I have not been able to do it through Format-Cells
menu.

160526 16:05:26
90928 9:09:28
92155 9:21:55
110101 11:01:01

Okay, I think you are asking for a way to change the data in the same
column
it is being placed in. You won't be able to do that with spreadsheet
formulas; so, if you are up for a macro solution, put the following code
in
the code window for the sheet you want it to operate on...

Sub FixColumnTime()
Dim R As Range
Dim C As Range
Set R = Range("C:C")
Application.EnableEvents = False
For Each C In R
If C.Text = "" Then
Exit For
ElseIf IsNumeric(C.Text) Then
C.Value = TimeSerial(Int(C.Value / 10000), _
Int((C.Value Mod 10000) / 100), _
C.Value Mod 100)
End If
Next
Application.EnableEvents = True
End Sub

Here, I have set the subroutine to operate on Column "C"... change the
Set
statement above to reflect the column you actually want to change. As
constructed, the code will execute down the column, changing pure numbers
only (so you can run the macros over already converted value without
affecting them), and will stop executing at the first empty cell it
encounters. To execute this code (once you have pasted it into your
worksheet), select Tools/Macro/Macros from the spreadsheet's menu (or
press
Alt+F8), select the FixColumnTime macro and click the Run button.

Rick

Rick