Time Formatting Problem when Pasting Data

Posted by Steven on August 06, 2001 7:24 AM

I have the cells set for time format hh:mm:ss. Most of the pasted data is already in that format. However, some of the pasted data only shows the seconds like :48. Excel does not add the 00:00 in front of the :48 to make it 00:00:48.
How can I make excel force these numbers to be in the correct format?

Posted by Mark W. on August 06, 2001 7:41 AM

If your time values exist in column A, the enter
the formula, =IF(ISTEXT(A1),"0:0"&A1,A1), in
column B. Select column B, and perform a
Copy/Paste/Special/Values. Next, Replace
(Control+H) all occurances of ":" with ":".
Finally, format column B as hh:mm:ss.