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]

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