ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Text to Time (https://www.excelbanter.com/excel-programming/399269-text-time.html)

build
Text to Time
 
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



joel
Text to Time
 
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" wrote:

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




build
Text to Time
 
Thanks Joel,
But that gives a "type mismatch"
cheers


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

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

joel
Text to Time
 
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

"build" wrote:

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


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

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

OssieMac
Text to Time
 
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


"Joel" wrote:

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

"build" wrote:

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


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

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

joel
Text to Time
 
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


"OssieMac" wrote:

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


"Joel" wrote:

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

"build" wrote:

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


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

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

Dave Peterson
Text to Time
 
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 wrote:

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

--

Dave Peterson

build
Text to Time
 
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


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


"OssieMac" wrote:

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


"Joel" wrote:

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

"build" wrote:

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


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

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

[email protected]
Text to Time
 
On Oct 13, 7:21 pm, "build" wrote:
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

"Joel" wrote in message

...

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

"OssieMac" wrote:

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

"Joel" wrote:

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

"build" wrote:

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

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

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

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.


All times are GMT +1. The time now is 10:18 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com