software

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))amp;quot;:quot;amp;(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

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)amp;quot;:quot;amp;right(d2,2).
--Bruce

quot;Marckquot; wrote:

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

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?

quot;bpeltzerquot; wrote:

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

Then go with the second option offered: =left(d2,2)amp;quot;:quot;amp;right(d2,2).quot;Marckquot; wrote:

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