Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
"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) |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
=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 |
#7
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert interval to various separate date , time, hr, minutes | Excel Worksheet Functions | |||
Time Function | Excel Discussion (Misc queries) | |||
How do I use 3 cells to create the string for a lookup function? | Excel Worksheet Functions | |||
CONVERT Function Disappered in Excel | Excel Discussion (Misc queries) | |||
Convert Time...!convert tenths of a second | Excel Discussion (Misc queries) |