Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: Convert number to days - hours - minutes

  1. #1
    Join Date
    5th November 2004
    Location
    San Francisco
    Posts
    15

    Convert number to days - hours - minutes

    Hello Excel Users

    I would like to convert a number (73.60) to days, hours & minutes to track how much sick or vacation time I have.

    Thanks
    Robert

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035
    This is fairly easy to do in Excel. Excel stores dates and time as numbers with each integer equal to one day. So 0.5 is 12 hours, for example. 1.5 would equal 36 hours.

    What units is your 73.60 in? If it's hours, divide it by 24 to convert it to Excel days and set your cell's format to CUSTOM > dd:hh:mm

    If it's in days already, just set the format.

    EDIT: Oops, I should have tested first. This format does not work for greater than 24 hours. Some fancier formulas are, I think, needed.

    [h]:mm will display hours (beyond 24) and minutes, but this is not what you asked.
    Best Regards,
    Tom
    ---------------------------
    Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

  3. #3
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035
    Adendum. If your 73.60 is in hours in cell A1, then in another cell (I'll use C1), enter:

    =INT(A1/24)

    to get the number of days.

    format another cell to hh:mm and enter:

    =(A1-24*D1)/24

    This will display the hours and minutes.
    Best Regards,
    Tom
    ---------------------------
    Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

  4. #4
    Join Date
    2nd August 2004
    Location
    Ontario - Canada
    Posts
    1,452
    Something like this might help you!
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Barbara - aka The Cat Lady

    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us, unless requested by me, will be deleted.

  5. #5
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035
    Adendum II:

    Also see Dave's tutorial on working with Dates and Times.

    http://www.ozgrid.com/Excel/ExcelDateandTimes.htm

    And (I forgot to say) Welcome to OzGrid
    Best Regards,
    Tom
    ---------------------------
    Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

  6. #6
    Join Date
    2nd August 2004
    Location
    Ontario - Canada
    Posts
    1,452
    Cool Tomach but just for clarity, I think you meant:
    Quote Originally Posted by thomach
    format D1 to hh:mm and in D1 enter:

    =(A1-24*C1)/24
    Barbara - aka The Cat Lady

    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us, unless requested by me, will be deleted.

  7. #7
    Join Date
    5th November 2004
    Location
    San Francisco
    Posts
    15

    Re: Convert number to days - hours - minutes

    That was awesome!

    Thank you very much thomach & Barbarr.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Convert Number To Hours & Minutes
    By Timbo in forum EXCEL HELP
    Replies: 13
    Last Post: February 1st, 2011, 13:31
  2. Convert Total Time To Days & Hours, Minutes
    By The Man Room in forum EXCEL HELP
    Replies: 6
    Last Post: April 24th, 2008, 20:10
  3. Format Total Hours to Days, Hours & Minutes
    By dpw in forum EXCEL HELP
    Replies: 9
    Last Post: September 6th, 2007, 04:28
  4. Replies: 5
    Last Post: December 5th, 2006, 07:41
  5. Replies: 3
    Last Post: December 5th, 2006, 01:50

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
  •  
porno