Total Minutes and converting to Hours and Minutes

[PSULionRP]

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

 

[FSt1]

hi
one way
A2 = 65
=ROUNDDOWN(A2/60,0)&" hours "&MOD(A2,60)&" minutes"

regards
FSt1

 

[Kevin B]

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.

 

[Pete_UK]

If your formula is like this, say:

=SUM(A2:A50)

then change it to:

=SUM(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

 

[PSULionRP]

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.

 

[David Biddulph]

=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"