Convert a number to hours, minutes and seconds

[GB]

Hi,

I have imported some data that comes in as a number that I need to convert to h:mm. The data string will be either 1,3,4,5,6 integers long and looks like this...eg


Imported data Need to equal this

Number h:mm

0 0:00

100 0:01

1000 0:10

10900 1:09

235900 23:59


Can someone please provide a smart formula to convert this (assume data is in cell A1).

Thanks

Birko

 

[Hui]

GB

Have you tried using Text to Columns on the Data Ribbon

Use a delimited Data Type

Use a space as a seperator

Excel should identify the second column as times


If you really want to use a formula try:

=TIMEVALUE(RIGHT(A1,LEN(A1)-FIND(" ",A1)))

then apply a custom Number Format of HH:MM;SS etc

Copy down

 

[GB]

Hi Hui,

sorry my post did not display as I expected which confused the matter a little. My numbers come like the following in string lengths of either 1,3,4,5,6. See these example numbers directly below, then further down their equivalent time in h:mm.

0

100

1000

10900

235900


which should equal...

0:00

0:01

0:10

1:09

23:59


Cheers

Birko

 

[Hui]

=(LEFT(TEXT(A1,"000000"),2)/24)+(MID(TEXT(A1,"000000"),3,2)/1440)+(RIGHT(TEXT(A1,"000000"),2)/(24*3600))

Then copy down

 

[GB]

Hi Hui,

perfect, thanks so much.

regards

Birko