richard.ca
Board Regular
- Joined
- Nov 7, 2007
- Messages
- 100
Can't quite get my head around how Excel stores and manipulates time values.
Here's my problem. I'm doing some time arithmetic and the time values are read
in from a file in "hh:mm:ss" format. Initially I was writing this directly to a cell and
then making a temporary copy for use later, code goes something like this:
Dim off_time As Double
off_time = Cells(row, n).Value
This worked but there was a performance impact since it was repeated many times in a
large loop. To speed things up I want to read the string time value into a code variable
instead of into a cell, like this:
Dim time_str as string
' load time into time_str etc
Now I have a problem because if I try to assign off_time = time_str of course I get a
run-time error due to imcompatible types. Excel obviously does some implicit type
conversion in the case of off_time = Cells(row, n).Value. How can I convert time_str
into the internal Excel time representation. I want to say something like:
off_time = convert_time(time_str)
but haven't found any suitable built-in function.
I would be grateful for any suggestions.
Many thanks,
Richard.
Here's my problem. I'm doing some time arithmetic and the time values are read
in from a file in "hh:mm:ss" format. Initially I was writing this directly to a cell and
then making a temporary copy for use later, code goes something like this:
Dim off_time As Double
off_time = Cells(row, n).Value
This worked but there was a performance impact since it was repeated many times in a
large loop. To speed things up I want to read the string time value into a code variable
instead of into a cell, like this:
Dim time_str as string
' load time into time_str etc
Now I have a problem because if I try to assign off_time = time_str of course I get a
run-time error due to imcompatible types. Excel obviously does some implicit type
conversion in the case of off_time = Cells(row, n).Value. How can I convert time_str
into the internal Excel time representation. I want to say something like:
off_time = convert_time(time_str)
but haven't found any suitable built-in function.
I would be grateful for any suggestions.
Many thanks,
Richard.
