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

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

I'm running out of steam!

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

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

Dave

"doveness" wrote in message
...
I'm trying to conver 425033 to 42:50:30

I'm running out of steam!

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

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

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

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
--
Gary's Student


"Dave Sheldon" wrote:

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

Dave

"doveness" wrote in message
...
I'm trying to conver 425033 to 42:50:30

I'm running out of steam!

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

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)&":"&R IGHT(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)&":"&R IGHT(A1;2);LEFT(A1;2)&":"&MID(A1;3;2)&":"&RIGHT(A1 ;2)))

*gublues


Gary''s Student skrev:

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
--
Gary's Student


"Dave Sheldon" wrote:

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

Dave

"doveness" wrote in message
...
I'm trying to conver 425033 to 42:50:30

I'm running out of steam!

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

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.
--
Gary's Student


"gublues" wrote:

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)&":"&R IGHT(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)&":"&R IGHT(A1;2);LEFT(A1;2)&":"&MID(A1;3;2)&":"&RIGHT(A1 ;2)))

*gublues


Gary''s Student skrev:

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
--
Gary's Student


"Dave Sheldon" wrote:

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

Dave

"doveness" wrote in message
...
I'm trying to conver 425033 to 42:50:30

I'm running out of steam!

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

A simpler way.....

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

format as [h]:mm:ss


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=545686