Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Loungers,

    Is there a way (I assume in cell formatting) to convert a number, ie 667.5 to be 667.30 (667 hours & 30 mins)

    Any thoughts

  2. #2
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hello - Would something like this do what is needed?

    [attachment=90439:Number to Hours-minutes.jpg]
    Attached Images Attached Images

  3. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Tim,

    That looks like a good option.

    Do you know if there is a way to achieve the same result using cell formatting?

    Cheers

  4. #4
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hello verada - Many options are available depending on what is going to be done with the info.

    Here are a couple of options ....

    [attachment=90441:Number to Hours-minutes 2.jpg]

    Column A can be hidden for different appearance.
    Attached Images Attached Images

  5. #5
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Thanks for the options - they will be very helpful.

    The spreadsheet I have is fairly complex, so was hoping not to have to insert additional columns to perform the calculation and was hoping that the same outcome could be achieved through "cell formatting".

    Any idea if this is possible?

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You could convert the decimal hours into days and then format as hours.

    You can convert the values into days by divding by 24. You can do this in an intermediate column or if you you don't want the intermediate column, put 24 into a cell, copy the cell, then select the cells that need to be converted, and edit- paste special and choose "divide" and they will be converted from hours into days. You can then format the cell with the custom format:
    [h]:mm

    to display the days as elapsed hours with minutes.

    Steve

  7. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I think what Verada's hoping for is conditional formatting, so he can enter "667.5" into a cell and have it immediately display as "667.30" or "667 hours and 30 minutes" without any other cells being involved.

    At least that's how I'm reading it ...?
    Beryl M


  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I think what Verada's hoping for is conditional formatting, so he can enter "667.5" into a cell and have it immediately display as "667.30" or "667 hours and 30 minutes" without any other cells being involved.
    And as the answers show it is not the formatting that needs to be adjusted but the value. If no other cells are desired to be involved then it seems to me that it would require a macro to change the number and divide it by 24 and then display it formatted as:
    [h]:mm

    The gist of all the answers is the need to change the value so that it can be formatted as desired. The date and time formats built into excel all work on units of days. To display correctly the value must be a day which is the hours / 24....

    Steve

Posting Permissions

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