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]

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]

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