Adding cells

[David]

In a spread sheet I have a group of cells, b4 thru b34 that represent
minutes but are entered as single digits. Example - b4 = 35, b5 = 15,
b6 = 55 and so on.
The entry cells are formatted as custom - 00. That way if I have an
entry of 5 or 0 it will read 05 or 00.
I need a formula that will give me the total of all these cells and
convert it into minutes.
If I have a total of 285 it should read 45 as in :45. I have tried
sum(b4:b34)/60 but it doesnt work on any formats.

What would be a good solution if there is one.
Thanks for any help.

 

[Bernie Deitrick]

David,

Format your cell for time, and use the formula

=SUM(B4:B34)/1440

1440 is the number of minutes in one day (24 hours * 60 minutes/hour). Excel
treats one day as 1, so that is the conversion.

HTH,
Bernie
MS Excel MVP

 

[Dave Will]

Thanks, it was close. I got the a result of 5:35 after totaling the
cells and setting the format to time but I need the 5 removed. Only need
it to read :35.


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Bernie Deitrick]

Dave,

=MINUTE(SUM(B4:B34)/1440)

HTH,
Bernie
MS Excel MVP

 

[Dave Will]

That did it, thanks again. If you would like I will send the spreadsheet
so you can see what I was getting at.
It might be a good sample for others to use. I was trying to stay away
from the time formats and make it easier for people to enter times
without using the " : ".
Just need an address.


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Bernie Deitrick]

Dave,

That really isn't necessary - I usually make my own spreadsheets.

Thanks anyway,
Bernie
MS Excel MVP