Tags: converting, display, excel, microsoft, msdn, software, spreadsheet, summing, total

Total Minutes and converting to Hours and Minutes

On Microsoft » Microsoft Excel

4,242 words with 5 Comments; publish: Thu, 22 May 2008 05:53:00 GMT; (30662.50, « »)

I have an excel spreadsheet and I am summing the minutes. So I have total

minutes of "65". How can I get this to display as "1:05"? Or Better Yet, "1

hour and 15 minutes"?

Any help is greatly appreciated.

Thanks!

PSULionRP

All Comments

Leave a comment...

  • 5 Comments
    • hi

      one way

      A2 = 65

      =ROUNDDOWN(A2/60,0)&" hours "&MOD(A2,60)&" minutes"

      regards

      FSt1

      "PSULionRP" wrote:

      > I have an excel spreadsheet and I am summing the minutes. So I have total

      > minutes of "65". How can I get this to display as "1:05"? Or Better Yet, "1

      > hour and 15 minutes"?

      > Any help is greatly appreciated.

      > Thanks!

      > PSULionRP

      #1; Thu, 22 May 2008 05:54:00 GMT
    • This should do the trick:

      =IF(A1<60,A1&" minutes",INT(A1/60)&" hours and "&MOD(A1,60)&" minutes")

      The IF checks to see if cell A1 has a value that is less than 60, and if

      true returns only the minutes. Otherwise it divides the value in A1 by 60,

      returning only the integer portion of the division to get the hours, and

      using the MOD function it divides the value in A1 by 60 and return the

      remainder to get the minutes.

      --

      Kevin Backmann

      "PSULionRP" wrote:

      > I have an excel spreadsheet and I am summing the minutes. So I have total

      > minutes of "65". How can I get this to display as "1:05"? Or Better Yet, "1

      > hour and 15 minutes"?

      > Any help is greatly appreciated.

      > Thanks!

      > PSULionRP

      #2; Thu, 22 May 2008 05:55:00 GMT
    • If your formula is like this, say:

      =3DSUM(A2:A50)

      then change it to:

      =3DSUM(A2:A50)/24/60

      and then use a custom format on the cell of [h]:mm. Or you could make

      the custom format:

      [h]" hours and "m" minutes"

      Hope this helps.

      Pete

      On Apr 11, 9:26=A0pm, PSULionRP <PSULio....excel.questionfor.info.discussions.microsoft.com>

      wrote:

      > I have an excel spreadsheet and I am summing the minutes. So I have total

      > minutes of "65". How can I get this to display as "1:05"? Or Better Yet,= "1

      > hour and 15 minutes"?

      > Any help is greatly appreciated.

      > Thanks!

      > PSULionRP

      #3; Thu, 22 May 2008 05:56:00 GMT
    • =TIME(,A2,) and format as h:mm or

      =A2/24/60 and format as h:mm

      Assuming that you don't want "1 hour and 15 minutes" but would prefer h

      "hours and" mm "minutes" , you could use custom formatting:

      h "hours and" mm "minutes"

      which would give

      "1 hours and 05 minutes".

      It would be slightly more complicated to get a distinction between 1 "hour"

      and 2 or more "hours", but it could be done. You could, of course, cheat

      and format as

      h "hour(s) and" mm "minutes"

      --

      David Biddulph

      "PSULionRP" <PSULionRP.excel.questionfor.info.discussions.microsoft.com> wrote in message

      news:A49B42BA-EAD1-4739-91E8-79BF7C8624E8.excel.questionfor.info.microsoft.com...

      >I have an excel spreadsheet and I am summing the minutes. So I have total

      > minutes of "65". How can I get this to display as "1:05"? Or Better Yet,

      > "1

      > hour and 15 minutes"?

      > Any help is greatly appreciated.

      > Thanks!

      > PSULionRP

      #4; Thu, 22 May 2008 05:57:00 GMT
    • PERFECT!

      Thanks Kevin. I'll add that to my Knowledgebase.

      One other question. I am summarizing by week so I'm doing it the long way.

      If I want to create a new spreadsheet with the Week totals, how do I copy the

      data over without all the blanks between it?

      Thanks again Kevin.

      "Kevin B" wrote:

      > This should do the trick:

      > =IF(A1<60,A1&" minutes",INT(A1/60)&" hours and "&MOD(A1,60)&" minutes")

      > The IF checks to see if cell A1 has a value that is less than 60, and if

      > true returns only the minutes. Otherwise it divides the value in A1 by 60,

      > returning only the integer portion of the division to get the hours, and

      > using the MOD function it divides the value in A1 by 60 and return the

      > remainder to get the minutes.

      > --

      > Kevin Backmann

      >

      > "PSULionRP" wrote:

      > > I have an excel spreadsheet and I am summing the minutes. So I have total

      > > minutes of "65". How can I get this to display as "1:05"? Or Better Yet, "1

      > > hour and 15 minutes"?

      > >

      > > Any help is greatly appreciated.

      > >

      > > Thanks!

      > >

      > > PSULionRP

      #5; Thu, 22 May 2008 05:58:00 GMT