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" wrote in message
...
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

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

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" wrote in message
...
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