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

Robert,

Try

=A2/24/60/60

and format as time


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Robert Smith" <RobertSmith@discussions.microsoft.com> wrote in message
news:3C59D554-3861-4EDE-B11D-E37432CFE964@microsoft.com...
> 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

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" <RobertSmith@discussions.microsoft.com> wrote in message
news:3C59D554-3861-4EDE-B11D-E37432CFE964@microsoft.com...
>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

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