Anaother time format question

[Ron]

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

 

[Rick Rothstein]

Assuming A2 contains your "time" entry, put this in another cell...

=--("0"&A2)

and format that cell as Time (picking whichever time format you want).

 

[Jacob Skaria]

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

 

[Ron]

Jacob,

That worked like a charm. The time is stored perfectly.
I used your solution as it kept the time in the same cell.

Thanks to Rick also.

Ron