Function to convert Time String to Time

[Andibevan]

Hi there,

What excel formula would I use to convert a number string i.e. 143254 to a
time string, i.e. 14:32:54

I need it to be as a formula so it is automated and not a manual process.

Thanks in advance

Andi

 

[Ken Wright]

With your value in A1, in say B1

=TIME(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------

 

[Andibevan]

Thanks Mangesh

assuming that your string is in A1, use:

=TIME(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2))

format as time

Mangesh

 

[Dodo]

What excel formula would I use to convert a number string i.e. 143254
to a time string, i.e. 14:32:54

=TIME(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2))

and set the cell format to 13:30:55

 

[Mangesh Yadav]

assuming that your string is in A1, use:

=TIME(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2))

format as time

Mangesh

 

[Bob Phillips]

=TIME(INT(A21/10000),INT((A21-INT(A21/10000)*10000)/100),MOD(A21,100))

 

[Bob Phillips]

Note the other answer will fail on say 91015, they will give 19:01:15.

If you are going to use the string method you should use

=TIME(LEFT(A1,LEN(A1)-4),MID(A1,3,2),RIGHT(A1,2))