I have a cell in a spreadsheet that tots up the a column of figures which are actually minutes and turns them into hours -

=(Sum(A1:A10)/60) & " hours"

Currently, if the number of minutes is, say, 60, then this returns "1.333333333333 hours".

How do I amend the forumla so it returns "1.33 hours".

Better still, can I make the formula return "1 hour 20 minutes"

I have tried the Help, honest, but you guys are just so good....

If you change the cell format to a number with 2 decimal places it will return 1.33 hours.

There maybe a custom format which returns hours and minutes. I'll have a play

Edit: sorry, misremembered the formats - they are actually times in hours and minutes, not a count (if that makes sense)

=ROUNDDOWN(SUM(A1:A8)/60,0) & " hrs "& MOD(SUM(A1:A8),60) & " mins"

the above should give you

8 hrs 20 mins for example

Hope that helps,
Daniel

Instead of dividing the total by 60, divide by 1440.
Then format the cell with the result > Custom > h:mm

...or

format the cell with the result > Custom > [h]" hours"" "mm" minutes"

thats a more elegant solution actually.

Just set the custom format to

h "Hours" m "Minutes"

and it will give you what you want, plus you will be able to total on it etc

thats a more elegant solution actually.

Just set the custom format to

h "Hours" m "Minutes"

and it will give you what you want, plus you will be able to total on it etc


... not forgetting the division by 1440! :)

I have a cell in a spreadsheet that tots up the a column of figures which are actually minutes and turns them into hours -

=(Sum(A1:A10)/60) & " hours"

Currently, if the number of minutes is, say, 60, then this returns "1.333333333333 hours".

How do I amend the forumla so it returns "1.33 hours".

Better still, can I make the formula return "1 hour 20 minutes"

I have tried the Help, honest, but you guys are just so good....

Erm - I'd rather have a formula that thinks 60 minutes is one hour, rather than one hour twenty minutes.

Erm - I'd rather have a formula that thinks 60 minutes is one hour, rather than one hour twenty minutes.

but 1.33333 hours is 1 hour 20 minutes.....

Sorted! Thanks to everyone who helped - especially so given that you had to correctly deduce that there was a typo ('60' when I meant '80') in my original question....

but 1.33333 hours is 1 hour 20 minutes.....

Indeed. But the OP said "if the number of minutes is, say, 60, then this returns "1.333333333333 hours"." 60 minutes is 1.0 hrs.

Indeed. But the OP said "if the number of minutes is, say, 60, then this returns "1.333333333333 hours"." 60 minutes is 1.0 hrs.

DOH! sorry.... never even noticed that!

Just a tip that you won't find in the help files, it you put the custom format to h:mm and then put a square bracket around the hour so that it reads [h]:mm it will calculate across 24 hours for you. So, for instance if you want to know how many hours between 23:00 and 07:30 for calculating night shifts on timesheets you need the square bracket. Try it!!