How do I import text into time format?

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

hh:mm, Data, Data

that I need to import into Excel.

I cannot for the life of me figure out how to get Excel to import the hh:mm part as (24 hour format) time instead of text. Is it possible? If not is there any straight-forward way of converting a column of hh:mm text into Excel's time format? (I know I can write a conversion formula, but there must 开发者_如何学Pythonbe something built-in already right?)

Excel 2008 on OS X

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

=TIMEVALUE(A1)

where A1 is your date should do the trick.

If all of the times are formatted as hh:mm, it should be pretty simple.

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

All you're doing here is taking the text (Column A) and isolating the hour (Column B) and minute (Column C) and using the TIME function to put them back together.

You could even do it in a single formula:

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

It is too late to format the column as time after import but should be possible as part of the import when applying FROM TEXT.

Though not shown in the link, Step 3 of 3 should allow you to set the relevant Column data format to General and that import noon for example as 0.5 displayed as 12:00.

Suppose I have a file called Time.txt with a single column of times as follows:

22:13
09:25
12:20

I would like to read them into an excel s/sheet and place them in Range A1 to A3 and format them as hh:mm:ss. This code shows how this may be achieved:

(NB: You need to add a reference to the Microsoft Scripting Runtime to get this to work. In VB editor: Tools > References > Microsoft Scripting Runtime)

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