Ron
Try the below macro. Select the range of cells with time. and run...
Sub Macro()
Dim cell As Range
For Each cell In Selection
If Left(cell.Value, 1) = ":" Then
cell.Value = "00:" & Mid(cell.Value, 2)
End If
Next
End Sub
--
If this post helps click Yes
---------------
Jacob Skaria
"Ron" wrote:
I get text files from a phone switch. I then have a macro that does some
calculations and saves it as 2003 format .xls. I am using 2007.
The times come across like: 8:48, which is actually 8:48:00 AM. However,
some come across as :53, which stores as :53. I need it to be in the format
of 12:53:00 AM, for calculations. If I manually put a 0 in front, 0:53, it
stores fine, but I do not want to manually do this on hundreds of files,
hundreds of entries every month. The concatenate into a different cell, saves
it as text, so no calculations there.
Is there VB formula that will change :53 to 12:53:00 AM which can be run on
the whole file, changing only those that are less than a minute? If it can do
it in the same cell, all the better, but if not, that's ok, too.
Thanks,
Ron