Formatting Time to do Calculations within Excel

[Guest]

I am importing a text file that averages employee's time into a spreadsheet.
I am having problems calculating time because when I import this text file
into the spreadsheet it doesn't have the leading zero's. Example :29 instead
of 0:29 which is needed to do time calculations. Right now I have to add the
zero in front of the colon manually. Does any one know of a way to do this
with a formula or programmatically?

Thanks,


Maria

 

[Dave Peterson]

How about selecting that range (a column???) and just do:

Edit|replace
what: : (just a single colon)
with: 0: (zero followed by a colon)

(I got 29 minutes (00:29:00) when I did this.)

 

[Guest]

That works but the problem is that some cells within the spreadsheet have
correct time format if they are > 1:00 ex: 1:29. I can't do a replace
because the above will change to 10:290:00 AM. I need a calculation that
will filter out whatever has a number in front of the colon from the one that
doesn't

 

[Dave Peterson]

Maybe you can still do it--but limit your replacement to just Text.

Select your range again.
Edit|goto|special
click on Constants
and remove the checkbox from Numbers, logicals and errors
(keep Text checked)

Then Edit|replace.

This will screw up if you have an colons in any cells that contain text.

For example: This would cause trouble.