Formatting time from imported data

[Guest]

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

 

[Bernard Liengme]

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)),A1) 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

 

[Guest]

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.

 

[Guest]

Thank you Socratis - SUPER

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.