Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding cells
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding cells
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 "David" wrote in message m... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding cells
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding cells
Dave,
=MINUTE(SUM(B4:B34)/1440) HTH, Bernie MS Excel MVP "Dave Will" wrote in message ... 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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding cells
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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding cells
Dave,
That really isn't necessary - I usually make my own spreadsheets. Thanks anyway, Bernie MS Excel MVP "Dave Will" wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding 2 cells only if one is not over | Excel Discussion (Misc queries) | |||
Need help w/ adding cells and dividing SUM by number of cells | Excel Worksheet Functions | |||
Adding cells | Excel Discussion (Misc queries) | |||
Adding only cells with an X in it | Excel Discussion (Misc queries) | |||
Adding colour to a range of cells based on one of the cells v... | Excel Discussion (Misc queries) |