Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Total Minutes and converting to Hours and Minutes
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
Total Minutes and converting to Hours and Minutes
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Total Minutes and converting to Hours and Minutes
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Total Minutes and converting to Hours and Minutes
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 On Apr 11, 9:26*pm, 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
Posted to microsoft.public.excel.misc
|
|||
|
|||
Total Minutes and converting to Hours and Minutes
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Total Minutes and converting to Hours and Minutes
=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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting total minutes into hours and minutes in Excel | Excel Worksheet Functions | |||
Concerting total minutes to hours and minutes | Excel Discussion (Misc queries) | |||
Converting hours:minutes:seconds to just minutes | Excel Worksheet Functions | |||
converting Days Hours & minutes into just minutes in excel | Excel Discussion (Misc queries) | |||
add column of minutes, show total in hours & minutes | Excel Worksheet Functions |