converting number string into time

If A1 is a numeric value and that single digit minute / second values are stored with leading 0 then

=TEXT(A1,"00\:00\:00")+0
(cell format: [hh]:mm:ss)

where A1 holds value

Try this, with 93428 in cell A1

Please Login or Register  to view this content.

This will return a time that can then be formatted to suit

Hope this helps

FWIW, TIME approach will only work if value < 24 hours - the string based approach should work regardless.

or maybe
=TIMEVALUE(SUBSTITUTE(TEXT(A1,"00-00-00"),"-",":"))
ah posted after donkeyote id been playing with that for a while! \: hmmmm

@mdw: same holds true of TIMEVALUE of course... if you remove the TIMEVALUE call and manually coerce resulting string (eg 0+) you can avoid the >= 24 hour issue.

Originally Posted by Marcol

All three methods seem to return the same result if the "string" has 5 or more characters (I tested up to1993428)

Not so.

Format the result cells as per earlier posts: [hh]:mm:ss (or General/Number)

You will find that TIME related functions only ever relate to the decimal remainder (integer is ignored).

The TEXT method with manual coercion (ie 0+ rather than use of TIME related function) will return the cumulative time - ie inclusive of complete days.

Hi DonkeyOte

Okay the penny's finally dropped.

I was testing with hh:mm:ss not [hh]:mm:ss and never thought of checking the results as general numbers.

Another lesson learned!

Thanks for the explanation, I hope it benefits paintballlovr as much as it has me.