How to import text in a temporary format?

Question

I have text files from a data logger in a format like this

hh:mm, Data, Data

which I need to import into Excel.

I can’t understand for life how to get Excel to import hh: mm time as (24-hour format) instead of text. Is it possible? If not, is there any direct way to convert a hh: mm text column to an Excel time format? (I know that I can write a transformation formula, but should there be something inline already correct?)

Excel 2008 on OS X

Answer 1

Not sure if there is a good way to do this with a button, but

=TIMEVALUE(A1)

A1 - , .

Answer 2

hh: mm, .

A       B           C            D
22:15   =LEFT(A1,2) =RIGHT(A1,2) =TIME(B2,C1,0)

, , - ( A) ( B) ( C) TIME .

:

=TIME(LEFT(A1,2),RIGHT(A1,2),0)

Answer 3

, FROM TEXT.

, 3 3 "", , , 0.5 12:00.

Answer 4

, Time.txt :

22:13
09:25
12:20

I would like to read them on an excel sheet and place them in the range from A1 to A3 and format them as hh:mm:ss. This code shows how this can be achieved:

(Note: you need to add a link to Microsoft Scripting Runtimeto make this work. In the VB editor: Tools > Links > Microsoft Scripting Duration )

Sub GetTimesFromFile()

Dim oFSO As New Scripting.FileSystemObject
Dim targetFile As Object
Set targetFile = oFSO.OpenTextFile("C:\Time.txt") //Change for your file path as appropriate

Dim cellCount As Long
cellCount = 1

Do Until targetFile.AtEndOfStream
    Cells(cellCount, 1) = targetFile.ReadLine
    Cells(cellCount, 1).NumberFormat = "hh:mm:ss" //Time format I want
    cellCount = cellCount + 1
Loop

End Sub