How do I import text into time format?

Question

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 be something built-in already right?)

Excel 2008 on OS X

What happens if you simply format the column as time after import? — Fionnuala, Dec 17 '10 at 19:54

score 3 · Accepted Answer · answered Dec 18 '10 at 08:39

3

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.

answered Dec 18 '10 at 08:39

Alex Andronov

1,665
2
15
18

1

Thanks. This looks good. I had just been hoping to avoid yet more columns in an already unwieldily spreadsheet. I'm annoyed because Excel will import dates correctly, but not times. – notMyScreenName Dec 18 '10 at 18:12

score 1 · Answer 2 · answered Dec 18 '10 at 18:14

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)

score 0 · Answer 3 · answered May 31 '15 at 02:10

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.

Alex P · Answer 4 · 2010-12-18T15:21:13.220

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

Thanks muchly for this. It is definitely going into my notes for future use... But unfortunately, Microsoft saw fit to remove VB from Mac Office 2008. It's back in Office 2011 but we haven't upgraded yet (it was also in Office 2004). — notMyScreenName, Dec 18 '10 at 17:58

How do I import text into time format?

4 Answers4