Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I convert time as text 'mm:ss to time value in Excel | Excel Discussion (Misc queries) | |||
Macro / function text time to 24hr excel time passed midnight fortotaling hr's | Excel Worksheet Functions | |||
Convert Text Time to Excel Time | Excel Discussion (Misc queries) | |||
convert time imported as text to time format for calculations | Excel Worksheet Functions | |||
Convert Text Time to Time | Excel Discussion (Misc queries) |