A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Convert minutes into hours and minutes



 
 
Thread Tools Display Modes
  #1  
Old May 2nd, 2005, 01:31 AM
southtxxbox
external usenet poster
 
Posts: n/a
Default Convert minutes into hours and minutes

I use a spreadsheet that totals minutes in different cells. I need those
totals added then converted into hours and minutes, NOT into days and hours
and minutes, and NOT into fractional hours. As an example, 1520 minutes
should be 25 hours and 20 minutes or 25:20. Days, hours, minutes (01:00:20)
does me no good, nor does fractional hour (25.33). I used the TEXT formula,
but found that minutes could not exceed 1440 (24 hours). In the example
above with 1520 minutes, it returned 01:20. The CONVERT formula falls short
since it only converts minutes into (fractional) hours and not minutes into
hours and minutes. Any help?
  #2  
Old May 2nd, 2005, 01:54 AM
Vasant Nanavati
external usenet poster
 
Posts: n/a
Default

Format the cell as Custom [h]:mm.

The formula in the cell shouLd be:

=SUM(A1:A100)/1440

--

Vasant



"southtxxbox" wrote in message
...
I use a spreadsheet that totals minutes in different cells. I need those
totals added then converted into hours and minutes, NOT into days and

hours
and minutes, and NOT into fractional hours. As an example, 1520 minutes
should be 25 hours and 20 minutes or 25:20. Days, hours, minutes

(01:00:20)
does me no good, nor does fractional hour (25.33). I used the TEXT

formula,
but found that minutes could not exceed 1440 (24 hours). In the example
above with 1520 minutes, it returned 01:20. The CONVERT formula falls

short
since it only converts minutes into (fractional) hours and not minutes

into
hours and minutes. Any help?



  #3  
Old May 2nd, 2005, 01:55 AM
Steve R
external usenet poster
 
Posts: n/a
Default

If your minutes are entered in non-time format, add them and divide by 1440.

Example: =SUM(A1:A19)/1440

Format the result [h]:mm

The site below will give you some understanding why this works how times are
stored in Excel.
http://www.ozgrid.com/Excel/ExcelDateandTimes.htm

Steve

"southtxxbox" wrote in message
...
I use a spreadsheet that totals minutes in different cells. I need those
totals added then converted into hours and minutes, NOT into days and
hours
and minutes, and NOT into fractional hours. As an example, 1520 minutes
should be 25 hours and 20 minutes or 25:20. Days, hours, minutes
(01:00:20)
does me no good, nor does fractional hour (25.33). I used the TEXT
formula,
but found that minutes could not exceed 1440 (24 hours). In the example
above with 1520 minutes, it returned 01:20. The CONVERT formula falls
short
since it only converts minutes into (fractional) hours and not minutes
into
hours and minutes. Any help?



  #4  
Old May 2nd, 2005, 04:46 AM
southtxxbox
external usenet poster
 
Posts: n/a
Default

Thanks, didn't really think anyone would respond. Just like that, it worked.

"Vasant Nanavati" wrote:

Format the cell as Custom [h]:mm.

The formula in the cell shouLd be:

=SUM(A1:A100)/1440

--

Vasant



"southtxxbox" wrote in message
...
I use a spreadsheet that totals minutes in different cells. I need those
totals added then converted into hours and minutes, NOT into days and

hours
and minutes, and NOT into fractional hours. As an example, 1520 minutes
should be 25 hours and 20 minutes or 25:20. Days, hours, minutes

(01:00:20)
does me no good, nor does fractional hour (25.33). I used the TEXT

formula,
but found that minutes could not exceed 1440 (24 hours). In the example
above with 1520 minutes, it returned 01:20. The CONVERT formula falls

short
since it only converts minutes into (fractional) hours and not minutes

into
hours and minutes. Any help?




  #5  
Old May 2nd, 2005, 04:48 AM
southtxxbox
external usenet poster
 
Posts: n/a
Default

Thanks for the info and the link. Cool stuff.

"Steve R" wrote:

If your minutes are entered in non-time format, add them and divide by 1440.

Example: =SUM(A1:A19)/1440

Format the result [h]:mm

The site below will give you some understanding why this works how times are
stored in Excel.
http://www.ozgrid.com/Excel/ExcelDateandTimes.htm

Steve

"southtxxbox" wrote in message
...
I use a spreadsheet that totals minutes in different cells. I need those
totals added then converted into hours and minutes, NOT into days and
hours
and minutes, and NOT into fractional hours. As an example, 1520 minutes
should be 25 hours and 20 minutes or 25:20. Days, hours, minutes
(01:00:20)
does me no good, nor does fractional hour (25.33). I used the TEXT
formula,
but found that minutes could not exceed 1440 (24 hours). In the example
above with 1520 minutes, it returned 01:20. The CONVERT formula falls
short
since it only converts minutes into (fractional) hours and not minutes
into
hours and minutes. Any help?




  #6  
Old March 29th, 2008, 10:09 AM posted to microsoft.public.excel.worksheet.functions
lsbsed
external usenet poster
 
Posts: 1
Default Convert minutes into hours and minutes

What if I want to display the converted hours and minutes as 25 h 20 min and
not as 25:20? When I tried to format the cell as Custom [h] h mm min Excel
though the first m of "min" meant minutes and displayed 25 h 20 20in. How to
go around this and display neatly time with text abbreviations 25 h 20 min?
  #7  
Old March 29th, 2008, 10:16 AM posted to microsoft.public.excel.worksheet.functions
Niek Otten
external usenet poster
 
Posts: 2,533
Default Convert minutes into hours and minutes

Put quotes around min

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"lsbsed" wrote in message ...
| What if I want to display the converted hours and minutes as 25 h 20 min and
| not as 25:20? When I tried to format the cell as Custom [h] h mm min Excel
| though the first m of "min" meant minutes and displayed 25 h 20 20in. How to
| go around this and display neatly time with text abbreviations 25 h 20 min?


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How can I convert fractional hours to minutes? Speck Teck General Discussion 3 March 4th, 2005 01:18 AM
MOD Function or how to convert total minutes to hours and minutes Brad Setting Up & Running Reports 1 December 22nd, 2004 09:07 AM
Convert hours into days hours minutes Alex General Discussion 1 September 14th, 2004 06:29 PM
Convert hours and minutes into minutes Jackie Parker Worksheet Functions 3 December 9th, 2003 04:14 PM


All times are GMT +1. The time now is 03:12 PM.


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