The best way would be to change the export program to add zero.

Otherwise, insert a new column next to the date column (I will assume the
first date is in A1)
Use =IF(LEFT(A1)=":",TIME(0,MID(A1,2,2),MID(A1,5,2)),A 1) in the empty cell
next to the 'error' column
Format as time and copy down the column by double clicking the fill handle
(solid square in lower right of active cell)
With all these selected use Copy followed by Paste Special | Values
Now you can delete the 'error' column
You could experiment with recording a macro to do this.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Wanna Learn" wrote in message
...
Hello everyday I import data that looks like this
ACD Time
8:52:40
:20:26
3:46:45
:00:00
1:17:52
3:27:37
Here is the problem if the cell is over an hour , example 8:52:40 the
cell
is formatted as time, but if the cell is less than an hour example, :20:20
is
formatted as general. However if I add a zero in front of the number
(e.g.
0:20:26)then it becomes formatted as time. I've have tried several
formats
to change this but it does not work. The only way is if I change it
manually
. On a daily basis I have over 50 that I have to add the zero to . How
can I speed this up. thanks