Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Text to time conversion
I have a text file that I want to import into excel. I have a column that is
a time field. (Ex. 142200)(hh:mm:ss). When I import it and format it in time, I always get 00:00:00. Is there a command that I need to convert it. Robert |
#2
|
|||
|
|||
Robert,
Try =A2/24/60/60 and format as time -- HTH RP (remove nothere from the email address if mailing direct) "Robert Smith" wrote in message ... I have a text file that I want to import into excel. I have a column that is a time field. (Ex. 142200)(hh:mm:ss). When I import it and format it in time, I always get 00:00:00. Is there a command that I need to convert it. Robert |
#3
|
|||
|
|||
Robert,
I don't think there's any way these numbers will be interpreted as hh:mm:ss with either opening or importing a text file. 142200, since there's no fractional part (right of the decimal point -- the part that indicates a fraction of a day, or time) will always be zero. This value is a date of 29 Apr 2289, at midnight. I think you'll have to open or import them as text (so you don't lose leading zeroes, e.g.: 042200 for 04:22:00), then use another column to convert them: =TIME(LEFT(A2,2),MID(A2,3,2), RIGHT(A2,2)) Then this column would be formatted for hh:mm:ss, or however you want to see it. -- Earl Kiosterud www.smokeylake.com "Robert Smith" wrote in message ... I have a text file that I want to import into excel. I have a column that is a time field. (Ex. 142200)(hh:mm:ss). When I import it and format it in time, I always get 00:00:00. Is there a command that I need to convert it. Robert |
#4
|
|||
|
|||
Another formula that will convert it:
=--TEXT(A1,"00\:00\:00") Still format as time. Robert Smith wrote: I have a text file that I want to import into excel. I have a column that is a time field. (Ex. 142200)(hh:mm:ss). When I import it and format it in time, I always get 00:00:00. Is there a command that I need to convert it. Robert -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculating time with text | Excel Worksheet Functions | |||
Text Wrapping | Excel Discussion (Misc queries) | |||
Conversion of a Number into English Text | Excel Worksheet Functions | |||
Changing Text to Time in Excel | Excel Discussion (Misc queries) | |||
Autofitting a row | Excel Discussion (Misc queries) |