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