Formating time

[Marta]

Hi,
I have a spreadsheet that imports time from another program and looks
somewhat like this:
Other Time ACW Time
4:35:20 1:23:07
:07:11 :28:03
:24:10 :00:00
:09:49 :07:19
:27:01 :06:19
:19:11 :00:00
:05:44 :12:35
:25:02 :00:00
:14:28 :09:16
1:08:00 :19:35
:12:13 :00:00
:17:54 :00:00
:07:53 :00:00
:36:44 :00:00

The problem I ran into is with the times that are less than an hour; they
all show up with a colon in front and not digit. This is causing a problem
when I try to add up the times - I get an error. I tried to format the cells
as time (13:30:55) but nothing happened.

Help!

TIA,
Marta

 

[Pete_UK]

I think you'll find that those times which start with a colon have
actually been imported as text values. You can get round this by using
this formula in a helper column (eg in C2):

=IF(LEFT(A2,1)=":","0"&A2,A2)*1

Format the cell as a time, copy C2 into D2 to act on B2, and then copy
these two down as far as required. You can fix the values and then
copy back over the originals, and then delete columns C and D.

Hope this helps.

Pete

 

[Shane Devenshire]

Hi,

Assume your time is in A1 then in a blank cel enter

=--(0&A1)

And copy and format as necessary