How do i format this time ?

[VICTOR]

Hi,

i have a column with times in this format, i.e.:

433
10433
190433

which respectively mean the follwoing times:

00 h: 04 min: 33 s
01 h: 04 min: 33 s
19 h: 04 min: 33 s

how can i set the cells so they show the time in the latter format ? I
welcome any suggestions. Regards,

Victor

 

[muddan madhu]

=LEFT(TEXT(A1,"000000"),2)& " hrs : " & MID(TEXT(A1,"000000"),3,2)&"
Min : "&RIGHT(A1,2)&" S"

 

[Pete_UK]

Assuming those numbers are in A1:A3, put this in B1:

=INT(A1/10000)/24+MOD(INT(A1/100),100)/24/60+MOD(A1,100)/24/60/60

Use Format | Cells | Custom to give the cell a format of:

hh" h: "mm" min: "ss" s"

and copy down to B3. You should get the following:

00 h: 04 min: 33 s
01 h: 04 min: 33 s
19 h: 04 min: 33 s

Hope this helps.

Pete

 

[VICTOR]

Assuming those numbers are in A1:A3, put this in B1:

=INT(A1/10000)/24+MOD(INT(A1/100),100)/24/60+MOD(A1,100)/24/60/60

Use Format | Cells | Custom to give the cell a format of:

hh" h: "mm" min: "ss" s"

and copy down to B3. You should get the following:

00 h: 04 min: 33 s
01 h: 04 min: 33 s
19 h: 04 min: 33 s

Hope this helps.

Pete

Thanks to all of you, it has finally worked.

 

[Pete_UK]

You're welcome, Victor - thanks for feeding back.

Pete

 

[Modeste]

Bonsour® VICTOR avec ferveur ;o))) vous nous disiez :

i have a column with times in this format, i.e.:
433
which respectively mean the follwoing times:

00 h: 04 min: 33 s
how can i set the cells so they show the time in the latter format ? I
welcome any suggestions. Regards,

Assuming times are in column "A"
in column "B" type :
=1*TEXT((1*A1),"00\:00\:00")
Now you have just to format column "B" as your favorite time format

Explanation :
1*A1 convert possibly numeric text value to pure numeric value
result is NUMERIC

TEXT(value,"00\:00\:00")
Convert numeric value to correct time value
result is TEXT !!!!!! "hh:mm:ss"

1*TEXT(num,format)
Convert text to Excel numeric internal time value
( ie : décimal value 0,00315972222 = 00:04:33)
result is standard numeric value

Apply your favorite time Format : hh" h "mm" min "ss" s"
result does not change (result is ready to time computation)
but display is now : 00 h 04 min 33 s