Converting Text to Time format of XX:XX

[Marck]

I have a question on converting number in text to time format of 24 hours.

The information is copied from another data file and gets pasted as a text
file of 4 digits. IE: 'XXXX

I tried the formula of =VALUE(LEFT(D2,2))&":"&(VALUE(RIGHT(D2,2)))
to get a time format but it is loosing #s when converting 0.

IE 0503 converted is 5:3
it misses the 2nd 0 Any suggestions to keep both 0 so it will come out
05:03???

any and all help would be appreciated.. Thanks

[bpeltzer]

If you want a real time value, as opposed to a text string that looks like a
time, you can use =time(left(d2,2),right(d2,2),0). If you just want to
insert the :, you can use =left(d2,2)&":"&right(d2,2).
--Bruce

"Marck" wrote:

> I have a question on converting number in text to time format of 24 hours.
>
> The information is copied from another data file and gets pasted as a text
> file of 4 digits. IE: 'XXXX
>
> I tried the formula of =VALUE(LEFT(D2,2))&":"&(VALUE(RIGHT(D2,2)))
> to get a time format but it is loosing #s when converting 0.
>
> IE 0503 converted is 5:3
> it misses the 2nd 0 Any suggestions to keep both 0 so it will come out
> 05:03???
>
> any and all help would be appreciated.. Thanks

[Marck]

Thank you but I need it to show just the 4 digits. No AM or PM after it.

0503 converted to 05:03

Thanks any suggestions?

"bpeltzer" wrote:

> If you want a real time value, as opposed to a text string that looks like a
> time, you can use =time(left(d2,2),right(d2,2),0). If you just want to
> insert the :, you can use =left(d2,2)&":"&right(d2,2).
> --Bruce
>
> "Marck" wrote:
>
> > I have a question on converting number in text to time format of 24 hours.
> >
> > The information is copied from another data file and gets pasted as a text
> > file of 4 digits. IE: 'XXXX
> >
> > I tried the formula of =VALUE(LEFT(D2,2))&":"&(VALUE(RIGHT(D2,2)))
> > to get a time format but it is loosing #s when converting 0.
> >
> > IE 0503 converted is 5:3
> > it misses the 2nd 0 Any suggestions to keep both 0 so it will come out
> > 05:03???
> >
> > any and all help would be appreciated.. Thanks

[bpeltzer]

Then go with the second option offered: =left(d2,2)&":"&right(d2,2).


"Marck" wrote:

> Thank you but I need it to show just the 4 digits. No AM or PM after it.
>
> 0503 converted to 05:03
>
> Thanks any suggestions?
>
> "bpeltzer" wrote:
>
> > If you want a real time value, as opposed to a text string that looks like a
> > time, you can use =time(left(d2,2),right(d2,2),0). If you just want to
> > insert the :, you can use =left(d2,2)&":"&right(d2,2).
> > --Bruce
> >
> > "Marck" wrote:
> >
> > > I have a question on converting number in text to time format of 24 hours.
> > >
> > > The information is copied from another data file and gets pasted as a text
> > > file of 4 digits. IE: 'XXXX
> > >
> > > I tried the formula of =VALUE(LEFT(D2,2))&":"&(VALUE(RIGHT(D2,2)))
> > > to get a time format but it is loosing #s when converting 0.
> > >
> > > IE 0503 converted is 5:3
> > > it misses the 2nd 0 Any suggestions to keep both 0 so it will come out
> > > 05:03???
> > >
> > > any and all help would be appreciated.. Thanks