Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    display number as hour and minute (Office 2000)

    Good Afternoon to all in the know,
    I am trying to find out if there is format for the cell that I can use to make a number display as a time format.
    Example == If a calculation results in the following ==== 48 I would like to show as :48 ---- or the number 72 --- I would like to show as 1:12

    Thanks in advance for the help
    and
    Have a Happy Thanksgiving <img src=/S/smile.gif border=0 alt=smile width=15 height=15> <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45>

  2. #2
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: display number as hour and minute (Office 2000

    Excel measures time in days, so it would recognise the number 72 as 72 days rather than 72 minutes. Therefore if you divide your number by 1330 (=60 x 24) and then apply time formatting (hh:mm) to the result, you should get what you're looking for.
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

  3. #3
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: display number as hour and minute (Office 2000

    Excel stores times as a fraction of 1 day, so you need to convert the minutes into the Excel format. You do this by dividing the answer by the number of minutes in a day, 1440

    This can then be formatted using a time format such as h:mm

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: display number as hour and minute (Office 2000

    1330 <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  5. #5
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: display number as hour and minute (Office 2000)

    TonyE -----> You are the MAN !!!

    Thanks,it is right on the money
    I just knew I would find the answer here ! [img]/forums/images/smilies/smile.gif[/img]

  6. #6
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: display number as hour and minute (Office 2000)

    Hello again guys,
    I ran into a problem that I can't get cleared. This process that you gave me works fine as long as the number to be displayed is less than 24:00. Once it gets to calculating the total number of hours accrued is 24:00 is shows it as 0:00, and then 25:00 would be showing 1:00 ---- What must I do to get the sequence to continue out to a max of 200:00?

    Thanks in advance

  7. #7
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: display number as hour and minute (Office 2000)

    hello, maybe i can help.
    would you be able to give me an example? and describe exactly what your after?

    thanks

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: display number as hour and minute (Office 2000)

    Select the cell or cells with the formula.
    Select Format | Cells...
    In the Number tab. select the Custom category.
    In the Type box, enter <code><!t>[h]<!/t>:mm</code>
    Click OK.
    The square brackets around h tell Excel to display accumulated hours instead of clock hours.

Posting Permissions

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