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

--
Rick (MVP - Excel)


"Ron" <(E-Mail Removed)> wrote in message
news:105BE7B2-8C01-4B18-917B-(E-Mail Removed)...
>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
>

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
>

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

"Jacob Skaria" wrote:

> 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
> >