Function to convert Time String to Time
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 |
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" wrote in message ... 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 |
Thanks Mangesh :-)
"Mangesh Yadav" wrote in message ... assuming that your string is in A1, use: =TIME(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2)) format as time Mangesh "Andibevan" wrote in message ... 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 |
"Andibevan" wrote in
: 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 -- It is I, DeauDeau (Free after monsieur Leclerc in 'Allo, 'allo) |
assuming that your string is in A1, use:
=TIME(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2)) format as time Mangesh "Andibevan" wrote in message ... 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 |
=TIME(INT(A21/10000),INT((A21-INT(A21/10000)*10000)/100),MOD(A21,100))
-- HTH Bob Phillips "Andibevan" wrote in message ... 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 |
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)) -- HTH Bob Phillips "Andibevan" wrote in message ... Thanks Mangesh :-) "Mangesh Yadav" wrote in message ... assuming that your string is in A1, use: =TIME(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2)) format as time Mangesh "Andibevan" wrote in message ... 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 |
All times are GMT +1. The time now is 10:49 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com