Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 33
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,942
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,316
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,856
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 33
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,651
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Converting total minutes into hours and minutes in Excel colette Excel Worksheet Functions 11 December 26th 07 08:24 PM
Concerting total minutes to hours and minutes Randi Excel Discussion (Misc queries) 7 November 7th 07 06:04 PM
Converting hours:minutes:seconds to just minutes Dan Vagle Excel Worksheet Functions 3 July 17th 06 11:20 PM
converting Days Hours & minutes into just minutes in excel Six Sigma Blackbelt Excel Discussion (Misc queries) 5 April 28th 06 09:45 PM
add column of minutes, show total in hours & minutes glider pilot Excel Worksheet Functions 1 December 30th 04 11:27 PM


All times are GMT +1. The time now is 07:51 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"