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

assuming that your string is in A1, use:

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

format as time

Mangesh



"Andibevan" <Andibevan@notehere-hotmail.com> wrote in message
news:uP6$T1KpFHA.1464@TK2MSFTNGP14.phx.gbl...
> 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" <mangesh.NOSPAMyadav@gmail.com> wrote in message
news:ef2G%235KpFHA.4088@TK2MSFTNGP15.phx.gbl...
> assuming that your string is in A1, use:
>
> =TIME(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2))
>
> format as time
>
> Mangesh
>
>
>
> "Andibevan" <Andibevan@notehere-hotmail.com> wrote in message
> news:uP6$T1KpFHA.1464@TK2MSFTNGP14.phx.gbl...
> > 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" <Andibevan@notehere-hotmail.com> wrote in message
news:eynSP7KpFHA.4088@TK2MSFTNGP15.phx.gbl...
> Thanks Mangesh :-)
>
>
> "Mangesh Yadav" <mangesh.NOSPAMyadav@gmail.com> wrote in message
> news:ef2G%235KpFHA.4088@TK2MSFTNGP15.phx.gbl...
> > assuming that your string is in A1, use:
> >
> > =TIME(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2))
> >
> > format as time
> >
> > Mangesh
> >
> >
> >
> > "Andibevan" <Andibevan@notehere-hotmail.com> wrote in message
> > news:uP6$T1KpFHA.1464@TK2MSFTNGP14.phx.gbl...
> > > 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" <Andibevan@notehere-hotmail.com> wrote in message
news:eynSP7KpFHA.4088@TK2MSFTNGP15.phx.gbl...
> Thanks Mangesh :-)
>
>
> "Mangesh Yadav" <mangesh.NOSPAMyadav@gmail.com> wrote in message
> news:ef2G%235KpFHA.4088@TK2MSFTNGP15.phx.gbl...
> > assuming that your string is in A1, use:
> >
> > =TIME(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2))
> >
> > format as time
> >
> > Mangesh
> >
> >
> >
> > "Andibevan" <Andibevan@notehere-hotmail.com> wrote in message
> > news:uP6$T1KpFHA.1464@TK2MSFTNGP14.phx.gbl...
> > > 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" <Andibevan@notehere-hotmail.com> wrote in message
news:eynSP7KpFHA.4088@TK2MSFTNGP15.phx.gbl...
> Thanks Mangesh :-)
>
>
> "Mangesh Yadav" <mangesh.NOSPAMyadav@gmail.com> wrote in message
> news:ef2G%235KpFHA.4088@TK2MSFTNGP15.phx.gbl...
> > assuming that your string is in A1, use:
> >
> > =TIME(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2))
> >
> > format as time
> >
> > Mangesh
> >
> >
> >
> > "Andibevan" <Andibevan@notehere-hotmail.com> wrote in message
> > news:uP6$T1KpFHA.1464@TK2MSFTNGP14.phx.gbl...
> > > 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" <Andibevan@notehere-hotmail.com> wrote in message
news:eynSP7KpFHA.4088@TK2MSFTNGP15.phx.gbl...
> Thanks Mangesh :-)
>
>
> "Mangesh Yadav" <mangesh.NOSPAMyadav@gmail.com> wrote in message
> news:ef2G%235KpFHA.4088@TK2MSFTNGP15.phx.gbl...
> > assuming that your string is in A1, use:
> >
> > =TIME(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2))
> >
> > format as time
> >
> > Mangesh
> >
> >
> >
> > "Andibevan" <Andibevan@notehere-hotmail.com> wrote in message
> > news:uP6$T1KpFHA.1464@TK2MSFTNGP14.phx.gbl...
> > > 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" <Andibevan@notehere-hotmail.com> wrote in message
news:eynSP7KpFHA.4088@TK2MSFTNGP15.phx.gbl...
> Thanks Mangesh :-)
>
>
> "Mangesh Yadav" <mangesh.NOSPAMyadav@gmail.com> wrote in message
> news:ef2G%235KpFHA.4088@TK2MSFTNGP15.phx.gbl...
> > assuming that your string is in A1, use:
> >
> > =TIME(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2))
> >
> > format as time
> >
> > Mangesh
> >
> >
> >
> > "Andibevan" <Andibevan@notehere-hotmail.com> wrote in message
> > news:uP6$T1KpFHA.1464@TK2MSFTNGP14.phx.gbl...
> > > 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" <mangesh.NOSPAMyadav@gmail.com> wrote in message
news:ef2G%235KpFHA.4088@TK2MSFTNGP15.phx.gbl...
> assuming that your string is in A1, use:
>
> =TIME(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2))
>
> format as time
>
> Mangesh
>
>
>
> "Andibevan" <Andibevan@notehere-hotmail.com> wrote in message
> news:uP6$T1KpFHA.1464@TK2MSFTNGP14.phx.gbl...
> > 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" <mangesh.NOSPAMyadav@gmail.com> wrote in message
news:ef2G%235KpFHA.4088@TK2MSFTNGP15.phx.gbl...
> assuming that your string is in A1, use:
>
> =TIME(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2))
>
> format as time
>
> Mangesh
>
>
>
> "Andibevan" <Andibevan@notehere-hotmail.com> wrote in message
> news:uP6$T1KpFHA.1464@TK2MSFTNGP14.phx.gbl...
> > 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" <mangesh.NOSPAMyadav@gmail.com> wrote in message
news:ef2G%235KpFHA.4088@TK2MSFTNGP15.phx.gbl...
> assuming that your string is in A1, use:
>
> =TIME(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2))
>
> format as time
>
> Mangesh
>
>
>
> "Andibevan" <Andibevan@notehere-hotmail.com> wrote in message
> news:uP6$T1KpFHA.1464@TK2MSFTNGP14.phx.gbl...
> > 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" <mangesh.NOSPAMyadav@gmail.com> wrote in message
news:ef2G%235KpFHA.4088@TK2MSFTNGP15.phx.gbl...
> assuming that your string is in A1, use:
>
> =TIME(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2))
>
> format as time
>
> Mangesh
>
>
>
> "Andibevan" <Andibevan@notehere-hotmail.com> wrote in message
> news:uP6$T1KpFHA.1464@TK2MSFTNGP14.phx.gbl...
> > 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
> >
> >
>
>

assuming that your string is in A1, use:

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

format as time

Mangesh



"Andibevan" <Andibevan@notehere-hotmail.com> wrote in message
news:uP6$T1KpFHA.1464@TK2MSFTNGP14.phx.gbl...
> 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
>
>

assuming that your string is in A1, use:

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

format as time

Mangesh



"Andibevan" <Andibevan@notehere-hotmail.com> wrote in message
news:uP6$T1KpFHA.1464@TK2MSFTNGP14.phx.gbl...
> 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
>
>

assuming that your string is in A1, use:

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

format as time

Mangesh



"Andibevan" <Andibevan@notehere-hotmail.com> wrote in message
news:uP6$T1KpFHA.1464@TK2MSFTNGP14.phx.gbl...
> 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
>
>

assuming that your string is in A1, use:

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

format as time

Mangesh



"Andibevan" <Andibevan@notehere-hotmail.com> wrote in message
news:uP6$T1KpFHA.1464@TK2MSFTNGP14.phx.gbl...
> 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" <Andibevan@notehere-hotmail.com> wrote in
news:uP6$T1KpFHA.1464@TK2MSFTNGP14.phx.gbl:

> 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)

"Andibevan" <Andibevan@notehere-hotmail.com> wrote in
news:uP6$T1KpFHA.1464@TK2MSFTNGP14.phx.gbl:

> 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)

"Andibevan" <Andibevan@notehere-hotmail.com> wrote in
news:uP6$T1KpFHA.1464@TK2MSFTNGP14.phx.gbl:

> 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)

"Andibevan" <Andibevan@notehere-hotmail.com> wrote in
news:uP6$T1KpFHA.1464@TK2MSFTNGP14.phx.gbl:

> 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)

"Andibevan" <Andibevan@notehere-hotmail.com> wrote in
news:uP6$T1KpFHA.1464@TK2MSFTNGP14.phx.gbl:

> 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)

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" <Andibevan@notehere-hotmail.com> wrote in message
news:uP6$T1KpFHA.1464@TK2MSFTNGP14.phx.gbl...
> 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" <Andibevan@notehere-hotmail.com> wrote in message
news:uP6$T1KpFHA.1464@TK2MSFTNGP14.phx.gbl...
> 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" <Andibevan@notehere-hotmail.com> wrote in message
news:uP6$T1KpFHA.1464@TK2MSFTNGP14.phx.gbl...
> 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" <Andibevan@notehere-hotmail.com> wrote in message
news:uP6$T1KpFHA.1464@TK2MSFTNGP14.phx.gbl...
> 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" <Andibevan@notehere-hotmail.com> wrote in message
news:uP6$T1KpFHA.1464@TK2MSFTNGP14.phx.gbl...
> 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" <Andibevan@notehere-hotmail.com> wrote in message
news:uP6$T1KpFHA.1464@TK2MSFTNGP14.phx.gbl...
> 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" <Andibevan@notehere-hotmail.com> wrote in message
news:uP6$T1KpFHA.1464@TK2MSFTNGP14.phx.gbl...
> 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" <Andibevan@notehere-hotmail.com> wrote in message
news:uP6$T1KpFHA.1464@TK2MSFTNGP14.phx.gbl...
> 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" <Andibevan@notehere-hotmail.com> wrote in message
news:uP6$T1KpFHA.1464@TK2MSFTNGP14.phx.gbl...
> 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" <Andibevan@notehere-hotmail.com> wrote in message
news:uP6$T1KpFHA.1464@TK2MSFTNGP14.phx.gbl...
> 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
>
>