Is there a simple formula to convert time in hundredths to minute

Discussion in 'Microsoft Excel Worksheet Functions' started by Ron, Jan 4, 2010.

  1. Ron

    Ron Guest

    I get an exported excel file that has the time in hours/hundredths and I have
    to convert it to hours/minutes - example 6.18 = 6:11, 6.15 = 6:09, etc.
    I'm doing the conversion but it takes me about 4 passes - is there a simple
    way to convert this kind of time. Thank You
     
    Ron, Jan 4, 2010
    #1
    1. Advertisements

  2. Ron

    Eva Guest

    Can you tell us more about it? I don't understand why 6.18 = 6:11 or 6.15 =
    6:09.
    --
    Greatly appreciated
    Eva


    "Ron" wrote:

    > I get an exported excel file that has the time in hours/hundredths and I have
    > to convert it to hours/minutes - example 6.18 = 6:11, 6.15 = 6:09, etc.
    > I'm doing the conversion but it takes me about 4 passes - is there a simple
    > way to convert this kind of time. Thank You
     
    Eva, Jan 4, 2010
    #2
    1. Advertisements

  3. Ron,

    You have a number of options, depending on what you need. The simplest is to format a cell for
    time, and in the cell use a formula like

    =A2/24

    Where A2 has the 6.18 value.

    HTH,
    Bernie
    MS Excel MVP


    "Ron" <> wrote in message
    news:...
    >I get an exported excel file that has the time in hours/hundredths and I have
    > to convert it to hours/minutes - example 6.18 = 6:11, 6.15 = 6:09, etc.
    > I'm doing the conversion but it takes me about 4 passes - is there a simple
    > way to convert this kind of time. Thank You
     
    Bernie Deitrick, Jan 4, 2010
    #3
  4. Ron

    Fred Smith Guest

    So you want to convert decimal hours to an Excel time? If so, you want:
    =a1/24
    Format as time.

    Regards,
    Fred

    "Ron" <> wrote in message
    news:...
    >I get an exported excel file that has the time in hours/hundredths and I
    >have
    > to convert it to hours/minutes - example 6.18 = 6:11, 6.15 = 6:09, etc.
    > I'm doing the conversion but it takes me about 4 passes - is there a
    > simple
    > way to convert this kind of time. Thank You
     
    Fred Smith, Jan 4, 2010
    #4
  5. It's pretty simple, Eva.
    6.15 is 6 hours and 15 hundredths.
    There are 60 minutes in an hour, so 15 hundredths of an hour is 0.15*60 = 9
    minutes, hence 6.15 is 6:09

    The answer to Ron's question is that the way to do the conversion is =A1/24,
    and format the result in a time format to suit your preferences.
    --
    David Biddulph

    "Eva" <> wrote in message
    news:...
    > Can you tell us more about it? I don't understand why 6.18 = 6:11 or 6.15
    > =
    > 6:09.
    > --
    > Greatly appreciated
    > Eva
    >
    >
    > "Ron" wrote:
    >
    >> I get an exported excel file that has the time in hours/hundredths and I
    >> have
    >> to convert it to hours/minutes - example 6.18 = 6:11, 6.15 = 6:09, etc.
    >> I'm doing the conversion but it takes me about 4 passes - is there a
    >> simple
    >> way to convert this kind of time. Thank You
     
    David Biddulph, Jan 4, 2010
    #5
  6. Ron

    Roger Govier Guest

    Hi Ron

    Excel stores it's times as fractions of a day (24 hours)
    Format the cell as hh:mm
    then with 6.15 in cell A1 =A1/24


    --
    Regards
    Roger Govier

    "Ron" <> wrote in message
    news:...
    > I get an exported excel file that has the time in hours/hundredths and I
    > have
    > to convert it to hours/minutes - example 6.18 = 6:11, 6.15 = 6:09, etc.
    > I'm doing the conversion but it takes me about 4 passes - is there a
    > simple
    > way to convert this kind of time. Thank You
    >
    > __________ Information from ESET Smart Security, version of virus
    > signature database 4743 (20100104) __________
    >
    > The message was checked by ESET Smart Security.
    >
    > http://www.eset.com
    >
    >
    >


    __________ Information from ESET Smart Security, version of virus signature database 4743 (20100104) __________

    The message was checked by ESET Smart Security.

    http://www.eset.com
     
    Roger Govier, Jan 4, 2010
    #6
  7. Ron

    Joe User Guest

    "Ron" wrote:
    > I get an exported excel file that has the time in
    > hours/hundredths and I have to convert it to
    > hours/minutes - example 6.18 = 6:11, 6.15 = 6:09, etc.


    Well, the "simple" answer is: =A1/24

    with the Custom format [h]:mm .

    But 6.18 will display as 6:10, not 6:11. The reason is: 60*0.18 = 10.8
    sec, and generally, Excel truncates seconds when displaying with an hh:mm
    format [1].

    So if you really want 6:11, you should do:

    =ROUND(A1*60,0)/1440

    where 1440 is 60*24.

    -----
    Endnotes

    [1] I said that Excel "generally" truncates seconds when displaying hh:mm.
    Actually, it sorta-rounds seconds, then truncates. So 6:10:59 (hh:mm:ss) is
    displayed as 6:10 (hh:mm), but 6:10:59.51 is displayed as 6:11. I say "sorta
    round" because 6:10:59.5 is displayed as 6:10.
     
    Joe User, Jan 4, 2010
    #7
  8. Ron

    Tbazemore Guest

    Re: Is there a simple formula to convert time in hundredths to min

    Roger,
    I have attempted using =A1/24, but its not working.... maybe im putting it
    in the wrong place? is it possible to elaborate a little more on the "how
    to"? I have also been attempting this coversion for my monthly report.

    Thanks.


    "Roger Govier" wrote:

    > Hi Ron
    >
    > Excel stores it's times as fractions of a day (24 hours)
    > Format the cell as hh:mm
    > then with 6.15 in cell A1 =A1/24
    >
    >
    > --
    > Regards
    > Roger Govier
    >
    > "Ron" <> wrote in message
    > news:...
    > > I get an exported excel file that has the time in hours/hundredths and I
    > > have
    > > to convert it to hours/minutes - example 6.18 = 6:11, 6.15 = 6:09, etc.
    > > I'm doing the conversion but it takes me about 4 passes - is there a
    > > simple
    > > way to convert this kind of time. Thank You
    > >
    > > __________ Information from ESET Smart Security, version of virus
    > > signature database 4743 (20100104) __________
    > >
    > > The message was checked by ESET Smart Security.
    > >
    > > http://www.eset.com
    > >
    > >
    > >

    >
    > __________ Information from ESET Smart Security, version of virus signature database 4743 (20100104) __________
    >
    > The message was checked by ESET Smart Security.
    >
    > http://www.eset.com
    >
    >
    >
     
    Tbazemore, Jan 4, 2010
    #8
  9. Ron

    Fred Smith Guest

    Re: Is there a simple formula to convert time in hundredths to min

    You need to change A1 to whatever cell your hours are in. As you did not
    specify any cell addresses, it's impossible for us to know where your data
    is. So we use A1 as a sample.

    If you still need help, tell us where your data is (ie E1, A27:A46), and
    we'll give you a specific formula.

    Regards,
    Fred

    "Tbazemore" <> wrote in message
    news:...
    > Roger,
    > I have attempted using =A1/24, but its not working.... maybe im putting it
    > in the wrong place? is it possible to elaborate a little more on the "how
    > to"? I have also been attempting this coversion for my monthly report.
    >
    > Thanks.
    >
    >
    > "Roger Govier" wrote:
    >
    >> Hi Ron
    >>
    >> Excel stores it's times as fractions of a day (24 hours)
    >> Format the cell as hh:mm
    >> then with 6.15 in cell A1 =A1/24
    >>
    >>
    >> --
    >> Regards
    >> Roger Govier
    >>
    >> "Ron" <> wrote in message
    >> news:...
    >> > I get an exported excel file that has the time in hours/hundredths and
    >> > I
    >> > have
    >> > to convert it to hours/minutes - example 6.18 = 6:11, 6.15 = 6:09, etc.
    >> > I'm doing the conversion but it takes me about 4 passes - is there a
    >> > simple
    >> > way to convert this kind of time. Thank You
    >> >
    >> > __________ Information from ESET Smart Security, version of virus
    >> > signature database 4743 (20100104) __________
    >> >
    >> > The message was checked by ESET Smart Security.
    >> >
    >> > http://www.eset.com
    >> >
    >> >
    >> >

    >>
    >> __________ Information from ESET Smart Security, version of virus
    >> signature database 4743 (20100104) __________
    >>
    >> The message was checked by ESET Smart Security.
    >>
    >> http://www.eset.com
    >>
    >>
    >>
     
    Fred Smith, Jan 4, 2010
    #9
  10. Ron

    Tbazemore Guest

    Is there a simple formula to convert time(hour/min) to (hour/hundr

    Okay... turns out I actually have the hours and minutes in cell A1 thru a15.
    I actually need the hour/hundredth)... Is there a formula? and if so, for A1
    where should I be putting this formula?

    Tabatha
    "Fred Smith" wrote:

    > You need to change A1 to whatever cell your hours are in. As you did not
    > specify any cell addresses, it's impossible for us to know where your data
    > is. So we use A1 as a sample.
    >
    > If you still need help, tell us where your data is (ie E1, A27:A46), and I need to covert it to hours/hundredths
    > we'll give you a specific formula.
    >
    > Regards,
    > Fred
    >
    > "Tbazemore" <> wrote in message
    > news:...
    > > Roger,
    > > I have attempted using =A1/24, but its not working.... maybe im putting it
    > > in the wrong place? is it possible to elaborate a little more on the "how
    > > to"? I have also been attempting this coversion for my monthly report.
    > >
    > > Thanks.
    > >
    > >
    > > "Roger Govier" wrote:
    > >
    > >> Hi Ron
    > >>
    > >> Excel stores it's times as fractions of a day (24 hours)
    > >> Format the cell as hh:mm
    > >> then with 6.15 in cell A1 =A1/24
    > >>
    > >>
    > >> --
    > >> Regards
    > >> Roger Govier
    > >>
    > >> "Ron" <> wrote in message
    > >> news:...
    > >> > I get an exported excel file that has the time in hours/hundredths and
    > >> > I
    > >> > have
    > >> > to convert it to hours/minutes - example 6.18 = 6:11, 6.15 = 6:09, etc.
    > >> > I'm doing the conversion but it takes me about 4 passes - is there a
    > >> > simple
    > >> > way to convert this kind of time. Thank You
    > >> >
    > >> > __________ Information from ESET Smart Security, version of virus
    > >> > signature database 4743 (20100104) __________
    > >> >
    > >> > The message was checked by ESET Smart Security.
    > >> >
    > >> > http://www.eset.com
    > >> >
    > >> >
    > >> >
    > >>
    > >> __________ Information from ESET Smart Security, version of virus
    > >> signature database 4743 (20100104) __________
    > >>
    > >> The message was checked by ESET Smart Security.
    > >>
    > >> http://www.eset.com
    > >>
    > >>
    > >>

    >
    > .
    >
     
    Tbazemore, Jan 4, 2010
    #10
  11. Ron

    Fred Smith Guest

    Re: Is there a simple formula to convert time(hour/min) to (hour/hundr

    You're sure making it difficult for people to provide help.

    First you say you have decimal hours, and want to convert to a time. Now you
    say you have a time, and need the decimal hours. So let's try again:

    1. In B1, enter =a1*24
    2. Format as a number with two decimal places
    2. Copy this formula down to B15

    You should now have your results in cells b1:b15. If you don't get the
    results you are looking for, it's probably because you have text, rather
    than a time. Convert the text to a time (by, for example, using Text To
    Columns) in this case.

    Regards,
    Fred

    "Tbazemore" <> wrote in message
    news:...
    > Okay... turns out I actually have the hours and minutes in cell A1 thru
    > a15.
    > I actually need the hour/hundredth)... Is there a formula? and if so, for
    > A1
    > where should I be putting this formula?
    >
    > Tabatha
    > "Fred Smith" wrote:
    >
    >> You need to change A1 to whatever cell your hours are in. As you did not
    >> specify any cell addresses, it's impossible for us to know where your
    >> data
    >> is. So we use A1 as a sample.
    >>
    >> If you still need help, tell us where your data is (ie E1, A27:A46), and
    >> I need to covert it to hours/hundredths
    >> we'll give you a specific formula.
    >>
    >> Regards,
    >> Fred
    >>
    >> "Tbazemore" <> wrote in message
    >> news:...
    >> > Roger,
    >> > I have attempted using =A1/24, but its not working.... maybe im putting
    >> > it
    >> > in the wrong place? is it possible to elaborate a little more on the
    >> > "how
    >> > to"? I have also been attempting this coversion for my monthly report.
    >> >
    >> > Thanks.
    >> >
    >> >
    >> > "Roger Govier" wrote:
    >> >
    >> >> Hi Ron
    >> >>
    >> >> Excel stores it's times as fractions of a day (24 hours)
    >> >> Format the cell as hh:mm
    >> >> then with 6.15 in cell A1 =A1/24
    >> >>
    >> >>
    >> >> --
    >> >> Regards
    >> >> Roger Govier
    >> >>
    >> >> "Ron" <> wrote in message
    >> >> news:...
    >> >> > I get an exported excel file that has the time in hours/hundredths
    >> >> > and
    >> >> > I
    >> >> > have
    >> >> > to convert it to hours/minutes - example 6.18 = 6:11, 6.15 = 6:09,
    >> >> > etc.
    >> >> > I'm doing the conversion but it takes me about 4 passes - is there a
    >> >> > simple
    >> >> > way to convert this kind of time. Thank You
    >> >> >
    >> >> > __________ Information from ESET Smart Security, version of virus
    >> >> > signature database 4743 (20100104) __________
    >> >> >
    >> >> > The message was checked by ESET Smart Security.
    >> >> >
    >> >> > http://www.eset.com
    >> >> >
    >> >> >
    >> >> >
    >> >>
    >> >> __________ Information from ESET Smart Security, version of virus
    >> >> signature database 4743 (20100104) __________
    >> >>
    >> >> The message was checked by ESET Smart Security.
    >> >>
    >> >> http://www.eset.com
    >> >>
    >> >>
    >> >>

    >>
    >> .
    >>
     
    Fred Smith, Jan 4, 2010
    #11
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. JD Fields

    military time conversion hundredths

    JD Fields, Sep 10, 2003, in forum: Microsoft Excel Worksheet Functions
    Replies:
    1
    Views:
    604
    JohnI in Brisbane
    Sep 10, 2003
  2. Guest

    Time in hours.hundredths

    Guest, Apr 25, 2004, in forum: Microsoft Excel Worksheet Functions
    Replies:
    2
    Views:
    428
    Guest
    Apr 25, 2004
  3. Guest

    convert 100 minute hour to a 60 minute hour

    Guest, Nov 15, 2004, in forum: Microsoft Excel Worksheet Functions
    Replies:
    1
    Views:
    573
    Frank Kabel
    Nov 15, 2004
  4. Guest

    Formatting Time with Hundredths of Seconds

    Guest, May 5, 2005, in forum: Microsoft Excel Worksheet Functions
    Replies:
    1
    Views:
    239
    Fred Smith
    May 5, 2005
  5. Tom

    ROUND() to tens or hundreds (not tenth/hundredths)!

    Tom, Nov 29, 2008, in forum: Microsoft Excel Worksheet Functions
    Replies:
    2
    Views:
    236
    Shane Devenshire
    Nov 29, 2008
Loading...

Share This Page