Change Military time on Imported Cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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(A1)=6,MID(A1,3,2),MID(A1,2,2)),RIGHT(A1,2))

format as h:mm:ss and drag down

Regards

Trevor
 
Rick

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

Regards

Trevor
 
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
 
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
Click to expand...

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
 
Back
Top