how do I convert a general number to a time format?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

doveness - 25 May 2006 22:40 GMT

I'm trying to conver 425033 to 42:50:30

I'm running out of steam!

Reply to this Message

Dave Sheldon - 25 May 2006 22:50 GMT

You can parse the string with
=LEFT(A1,2)&":"&MID(A1,3,2)&":"&RIGHT(A1,2)

Dave

> I'm trying to conver 425033 to 42:50:30
>
> I'm running out of steam!

Reply to this Message

Pete_UK - 25 May 2006 22:59 GMT

Dave's formula will return a text result which will look like a time.
If you want the result in true Excel time format (numeric) then you
will have to put VALUE( ... ) around his formula and format the cell
using a custom format of [hh]:mm:ss.

Hope this helps.

Pete

Reply to this Message

Gary''s Student - 25 May 2006 23:09 GMT

Hi Dave:

Good answer.

A slight variation will give a time in standard numerical format:

=LEFT(A1,2)/24+MID(A1,3,2)/(24*60)+RIGHT(A1,2)/(24*60*60)
format as [hh]:mm:ss

Signature

Gary's Student

> You can parse the string with
> =LEFT(A1,2)&":"&MID(A1,3,2)&":"&RIGHT(A1,2)
[quoted text clipped - 4 lines]
> >
> > I'm running out of steam!

Reply to this Message

gublues - 26 May 2006 00:19 GMT

If you have hrs between 1 and 10 you will have only 5 numbers and only the
RIGHT formula will give correct answer.
Then you have to modify your formula like this:
=IF(LEN(A1)=5;"0"&LEFT(A1;1)&":"&MID(A1;2;2)&":"&RIGHT(A1;2);LEFT(A1;2)&":"&MID(A1;3;2)&":"&RIGHT(A1;2))

if below 1 hrs you maybe have only 4.
Then you have to modify even further:
=IF(LEN(A1)=4;"00"&":"&MID(A1;1;2)&":"&RIGHT(A1;2);IF(LEN(A1)=5;"0"&LEFT(A1;1)&":"&MID(A1;2;2)&":"&RIGHT(A1;2);LEFT(A1;2)&":"&MID(A1;3;2)&":"&RIGHT(A1;2)))

*gublues

Gary''s Student skrev:

> Hi Dave:
>
[quoted text clipped - 13 lines]
> > >
> > > I'm running out of steam!

Reply to this Message

Gary''s Student - 26 May 2006 00:39 GMT

Your comments are correct. The formula is designed to handle 6 digit
quantities that can be mapped: hhmmss

It will fail for hours less than 10.
It will fail for hours greater than 99.

The formula will, however, handle numbers as per the OP's spec.

Signature

Gary's Student

> If you have hrs between 1 and 10 you will have only 5 numbers and only the
> RIGHT formula will give correct answer.
[quoted text clipped - 26 lines]
> > > >
> > > > I'm running out of steam!

Reply to this Message

daddylonglegs - 26 May 2006 00:39 GMT

A simpler way.....

=TEXT(A1,"00\:00\:00")+0

format as [h]:mm:ss

Signature

daddylonglegs

Reply to this Message