Re: Convert minutes into hours and minutes

Steve R

unread,

May 1, 2005, 8:55:02 PM5/1/05

to

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" <south...@discussions.microsoft.com> wrote in message
news:796510C7-92F6-4732...@microsoft.com...
>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?

Vasant Nanavati

unread,

May 1, 2005, 8:54:23 PM5/1/05

to

Format the cell as Custom [h]:mm.

The formula in the cell shouLd be:

=SUM(A1:A100)/1440

--

Vasant

southtxxbox

unread,

May 1, 2005, 11:46:02 PM5/1/05

to

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

southtxxbox

unread,

May 1, 2005, 11:48:02 PM5/1/05

to

Thanks for the info and the link. Cool stuff.

lsbsed

unread,

Mar 29, 2008, 5:09:00 AM3/29/08

to

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?

Niek Otten

unread,

Mar 29, 2008, 5:16:45 AM3/29/08

to

Put quotes around min

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"lsbsed" <lsb...@discussions.microsoft.com> wrote in message news:C4297FC0-67A1-45A3...@microsoft.com...

andy...@gmail.com

unread,

Apr 9, 2016, 8:17:11 PM4/9/16

to

[h]\h mm\m
example: 1h 36m

Auric__

unread,

Apr 11, 2016, 1:27:01 AM4/11/16

to

That question was asked ***EIGHT YEARS AGO!***

Don't you Google groupies know how to look at dates? You don't get bonus
points for answering old questions.

--
That takes a special type of stupid.

1010thsde...@gmail.com

unread,

May 2, 2016, 6:48:04 AM5/2/16

to

Today, in 2016, I had this very question and this page had the answer. While the original person maybe didn't need updates 8 years later, others may appreciate the additional information.

I guess it takes a special kind of smart to see that some things have enduring value.

neil.i...@gmail.com

unread,

Feb 19, 2019, 2:38:08 AM2/19/19

to

Today in 2019 I was having a duration formatting issue that Andy's answer resolved thank you Andy.