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

Solution 1

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.

Solution 2

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)