format number to time

Posted by Amanda on June 25, 2001 9:30 PM

Had to download sunrise/set times from internet.
Once pasted (text format) in excel, in one cell for example, is 628 (ie: 6.28am).

I need to convert this in to time format of hh:mm:ss so it's 06:28:00 I've tried the obvious things? What am i doing wrong?!!!
Thanks...

Posted by Russell on June 25, 2001 9:55 PM

Well, the only thing I could think of is to multiply your values by 100 (maybe in the next column) and then format the result as follows:

Go to format-cells. Then enter in this custom format: 00":"00":"00

Hope this helps,

Russell

00":"

Posted by mseyf on June 26, 2001 7:16 AM

you could also try (assuming time text is in A1):

=TIMEVALUE(LEFT(A1,LEN(A1)-2)&":"&RIGHT(A1,2))

HTH

Mark