Anaother time format question

  • Thread starter Thread starter Ron
  • Start date Start date
R

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
 
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).
 
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
 
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
 
Back
Top