Formatting time from imported data
 

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

Formatting time from imported data
 

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

Formatting time from imported data
 

Wanna,

another solution is to create this macro in a module.

Public Sub FormatTime()
Dim rng As range
Dim cell As range

Set rng = Selection

For Each cell In rng
If Left(cell.Value, 1) = ":" Then
cell.Value = "0" & cell.Value
End If
Next
End Sub

To make things easier, assign this macro to a shortcut, such as ctrl+g.
Thus, to use daily just highlight your range and press ctrl+g.

"Wanna Learn" wrote:

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

Formatting time from imported data
 

Thank you Socratis - SUPER

"Socratis" wrote:

Wanna,

another solution is to create this macro in a module.

Public Sub FormatTime()
Dim rng As range
Dim cell As range

Set rng = Selection

For Each cell In rng
If Left(cell.Value, 1) = ":" Then
cell.Value = "0" & cell.Value
End If
Next
End Sub

To make things easier, assign this macro to a shortcut, such as ctrl+g.
Thus, to use daily just highlight your range and press ctrl+g.

"Wanna Learn" wrote:

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