Results 1 to 5 of 5
  1. #1
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Converting Minutes to Hours:Minutes (excel)

    If I have a number in a cell that is the total number of minutes, say, 4899, is there an Excel Format/Function that would convert that number to 81:39?

    I've got my own formula that does the deed, but I was wondering whether Excel had a built-in Format or function that did the deed.

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

    Re: Converting Minutes to Hours:Minutes (excel)

    Dates and times are expressed as a number of days in Excel. So you must convert 4899 to days, by dividing it by 1440, the number of minutes in a day. Say, your number 4899 is in cell A1. In cell B1, enter the formula =A1/1440. You can format the result in B1 as [h]:mm - the brackets indicate that the number of hours can be above 24. Alternatively, you can enter the formula =Text(A1/1440,"[h]:mm") into cell B1. However, this will result in a text string that can't be used in further calculations.

  3. #3
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting Minutes to Hours:Minutes (excel)

    =a1/1440, and then separately applying the format works too, and allows the result to be used in further calculations.

    Currently, I'm using

    =INT(L2/60) & ":" & L2 - INT(L2/60)*60

    As I have no need for further calculations with the result.

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

    Re: Converting Minutes to Hours:Minutes (excel)

    Your
    <hr>=a1/1440, and then separately applying the format works too<hr>
    was the first solution in my reply.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting Minutes to Hours:Minutes (excel)

    Find an empty cell on the worksheet and enter the number 1440 (the number of minutes in a day). Select this cell and then select Copy from the Edit menu. Now, select all of the cells that contain the number of minutes. On the Edit menu click on "Paste Special". In the dialog box, in the Operation section click on Divide to put a dot in the radio button. Click on OK. Now, format the cells with the custom format [h]:mm:ss.
    Legare Coleman

Posting Permissions

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