+ Reply to Thread
Results 1 to 2 of 2

Time conversion problem

  1. #1
    Jennifer Ehrlich
    Guest

    Time conversion problem

    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



  2. #2
    Ron Rosenfeld
    Guest

    Re: Time conversion problem

    On Fri, 28 Jul 2006 02:25:00 GMT, "Jennifer Ehrlich" <ehrlichj2@earthlink.net>
    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1