Change number in to time

Hello, I am new to Excelforum.com and also a beginner to Office.

My question is i try to change the numbers in to min and sec like this here below.

Table row:

300 > in to this 3:00 and not like this > 00:03:00
1000 > in to this 10:00
10000 > in to this 1:00:00

that all automatic for the whole row.

Is that possible ?

Thanks in advance.

Hi,

I guess this can be done if we convert the numbers into text. Can you please upload your excel template here?

Please use the following formula in cell B1, hope it works for you..


=IF(A1>=10000,CONCATENATE(INT(A1/10000),":",RIGHT(INT(A1/100),2),":",RIGHT(A1,2)),CONCATENATE(INT(A1/100),":",RIGHT(A1,2)))


A B

300 3:00
1000 10:00
10000 1:00:00
12345 1:23:45


Regards,
Chandra

Hi Tasos112,

If it is number use this formula:
=TIME(INT(A1/10000),INT(MOD(A1,10000)/100),MOD(A1,100))

and if it is text:
=TIME(VALUE(LEFT(A1,2)),VALUE(MID(A1,3,2)),VALUE(RIGHT(A1,2)))

Note:: cell format should be h:mm:ss

HoPE Enjoy
MaSs

Hi all, i try to type this:

=IF(A1>=10000,CONCATENATE(INT(A1/10000),":",RIGHT(INT(A1/100),2),":",RIGHT(A1,2)),CONCATENATE(INT(A1/100),":",RIGHT(A1,2)))

I get this error. The formula you typed contains an error.

Thanks for the reply

Please see the attached file.