Text to Time

[build]

G'day All,
I'm very rusty with VBA.
I'm trying to convert text "1:28.871" to time "m:ss.000"
I've got:

Sub TEXT_TO_TIME()
For Each cell In ActiveRange
cell.Value = TimeValue(cell)
Next cell
End Sub

This gives me "run-time error '13':"
A google points to
"due to copied data being formatted as text rather than numbers"
but hey I know that.
Any Ideas?

Thanks in anticipation,
build

 

[Guest]

Use the isdate function to make sure cell is date format. Error 13 is caused
by non-date values being passed to datavalue

Sub TEXT_TO_TIME()
For Each cell In ActiveRange
if isdate(cell) then
cell.Value = TimeValue(cell)
end if
Next cell
End Sub

 

[build]

Thanks Joel,
But that gives a "type mismatch"
cheers

 

[Guest]

I think you need activecell.currentregion. Activerange isn't correct.


Sub TEXT_TO_TIME()
For Each cell In ActiveCell.CurrentRegion
If IsDate(cell) Then
cell.Value = TimeValue(cell)
End If
Next cell
End Sub

 

[Guest]

I don't think that TimeValue function can handle fractions of a second.

Because it appears that you are trying to convert text times to time values
I suggest that you enter your times on the worksheet as times and not as text.

Just as an added extra, if you need to manipulate fractions of a second on
the worksheet using VBA then you probably need to use a workaround something
like the following example:-

For Each cell In Range("A1:A9")
cell.Value = cell.Value + 0.15 / 86400
Next cell

The above adds 0.15 seconds to an existing time in the cell. (86400 seconds
in a day)

I included the added extra because your question indicated that you are
using fractions of a second and no doubt you need to manipulate them.

Regards,

OssieMac

 

[Guest]

You can try this

Sub TEXT_TO_TIME()
For Each cell In ActiveRange
cell.Value = TimeValue(left(cell,instr(cell,".") - 1))
Next cell
End Sub

 

[Dave Peterson]

That looks like it already is a time.

If you're data is really text and you want to convert it to times, you could
try:

Selecting the range
edit|Replace
what: : (colon)
with: : (same colon)
replace all
and give that range a custom format of
hh:mm:ss.000
(or whatever you want)

If you really want a macro, record one when you do it manually.

 

[build]

Thanks Guys,
I'm beginning to get the hang of time and the weird way excel renders it.
Unfortunately the data is from text files if not imported as text the times
lose 2 decimal places.
If I edit directly in the cell they also lose 2 decimal places.

Anyway it seems the first problem was with "ActiveRange" I changed it to
Selection. The next problem was TimeValue(cell) while it works in a cell
formula it appears to jamb in VBA so in consideration that simply double
clicking a cell converted the text to time and Ossies suggestion I tried
"cell.Value = cell.Value + 0.15 / 86400" that gave a type mismatch so I
tried the following:

Sub TEXT_TO_TIME()
For Each cell In Selection
cell.Value = cell.Value
Next cell
End Sub

It works solving the first step.
However as a matter of interest I tried:

Sub TEXT_TO_TIME()
For Each cell In Selection
cell.Value = cell.Value
cell.Value = cell.Value + 0.15 / 86400
Next cell
End Sub

Now the addition worked? Excel won't work with the text? Is there *force*
method?

Thanks ALL of you, I learnt something from every reply.
regards,
build

 

[chip.gorman]

Thanks Guys,
I'm beginning to get the hang of time and the weird way excel renders it.
Unfortunately the data is from text files if not imported as text the times
lose 2 decimal places.
If I edit directly in the cell they also lose 2 decimal places.

Anyway it seems the first problem was with "ActiveRange" I changed it to
Selection. The next problem was TimeValue(cell) while it works in a cell
formula it appears to jamb in VBA so in consideration that simply double
clicking a cell converted the text to time and Ossies suggestion I tried
"cell.Value = cell.Value + 0.15 / 86400" that gave a type mismatch so I
tried the following:

Sub TEXT_TO_TIME()
For Each cell In Selection
cell.Value = cell.Value
Next cell
End Sub

It works solving the first step.
However as a matter of interest I tried:

Sub TEXT_TO_TIME()
For Each cell In Selection
cell.Value = cell.Value
cell.Value = cell.Value + 0.15 / 86400
Next cell
End Sub

Now the addition worked? Excel won't work with the text? Is there *force*
method?

Thanks ALL of you, I learnt something from every reply.
regards,
build

Excel works with text, but what you want is for your text to be
treated as numbers. My solution would be to put a helper column in
with =VALUE("A1") or whatever and copy it down. That (I think) handles
the fractional seconds ok too. Unfortunately, the worksheet function
VALUE() is not available in VBA for this purpose.