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 |
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 |
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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 04:21 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com