Time conversion problem

[Jennifer Ehrlich]

I am trying to write some data analysis macro code and am having problems
with how Excel is and isn't storing the times. During a study a researcher
enters, for example 4:39. That needs to be 4 minutes and 39 seconds.
However, Excel formats it as 4 hours and 39 minutes - as "h:mm". I really
want it formatted as "hh:mm:ss" so it would be 00:04:39. If I go in and
change the cell format to "hh:mm:ss" it simply writes the value out as
4:39:00. I've tried switching back and forth between various date, general,
and text formats to no avail.

For a number of reasons, I may very well be stuck with not being able to
tell Excel in advance to format the cell as "hh:mm:ss" or get the
researchers to enter it in a long form so need to solve this through macro
code. How can I translate the 4 hours and 39 minutes in to 4 minutes and 39
seconds (ideally in the format of 00:04:39)?

Jenny

[Ron Rosenfeld]

On Fri, 28 Jul 2006 02:25:00 GMT, "Jennifer Ehrlich" <[email protected]>
wrote:

>I am trying to write some data analysis macro code and am having problems
>with how Excel is and isn't storing the times. During a study a researcher
>enters, for example 4:39. That needs to be 4 minutes and 39 seconds.
>However, Excel formats it as 4 hours and 39 minutes - as "h:mm". I really
>want it formatted as "hh:mm:ss" so it would be 00:04:39. If I go in and
>change the cell format to "hh:mm:ss" it simply writes the value out as
>4:39:00. I've tried switching back and forth between various date, general,
>and text formats to no avail.
>
>For a number of reasons, I may very well be stuck with not being able to
>tell Excel in advance to format the cell as "hh:mm:ss" or get the
>researchers to enter it in a long form so need to solve this through macro
>code. How can I translate the 4 hours and 39 minutes in to 4 minutes and 39
>seconds (ideally in the format of 00:04:39)?
>
>Jenny
>

So long as your researchers are unambiguous in how the enter the data, and that
4:39 is always going to be 4 minutes 39 seconds, all you need to do is divide
their entries by 60.

Format as hh:mm:ss


--ron