Convert Text to a time format 2

[akaivyleaf]

Hi all, I would like to convert a general number into a time format dynamically. Currently time is put in without colons separating the hours, minutes and seconds. It appears as such: 95904 I need the number to appear as 9:59:04 Is there an easy way of converting this text into a time format?

 

[JerryKlmns]

format(Left(TheInput, len(TheInput)-4) & ":" &
Left(Right(TheInput, 4) ,2) & ":" &
Right(TheInput, 2), "hh:nn:ss")

should return 09:59:04

where TheInput = 95904

 

[combo]

If you are in excel, and the input value in A1 is number:
=TIMEVALUE(TEXT(A1,"00"":""00"":""00"))

combo

 

[GlennUK]

To do it with sheet formulae instead of a macro, put this formula in a cell and copy down:

=TIMEVALUE(LEFT(A1,LEN(A1)-4)&":"&LEFT(RIGHT(A1,4),2)&":"&RIGHT(A1,2))

This gives Excel time values as results. Format as "hh:mm:ss" afterwards.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[akaivyleaf]

Thank you both for the excellent tip. They both worked and accomplished my goal. I appreciate it.