Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Adding minutes & seconds
I need to add a long list of minutes and seconds. (ex. 25:16, 16:39, 27:41)
I have looked at all the functions and cannot find one that works. They all want to add the times assuming they are hours and minutes or time on a clock. I want to find a function that would add up the previous example and display it as 69:36 total minutes and seconds. It seems like one of the problems I am running into is finding or creating my own function that knows to add up the seconds to 60, not 100 like most decimal functions are used to. Does anyone have an answer? I would be forever grateful! |
#2
|
|||
|
|||
You need to enter 0 for hours like
0:25:16 how else would excel know it is not 25 hours? Finally use a custom format of [mm]:ss or else you won't get the right display Regards, Peo Sjoblom "Emily16" wrote in message ... I need to add a long list of minutes and seconds. (ex. 25:16, 16:39, 27:41) I have looked at all the functions and cannot find one that works. They all want to add the times assuming they are hours and minutes or time on a clock. I want to find a function that would add up the previous example and display it as 69:36 total minutes and seconds. It seems like one of the problems I am running into is finding or creating my own function that knows to add up the seconds to 60, not 100 like most decimal functions are used to. Does anyone have an answer? I would be forever grateful! |
#3
|
|||
|
|||
Emily,
Using any formula to add time values works fine in Excel. I think the problem in your situation might be that when you enter in the format xx:yy, Excel assumes hours and minutes, not minutes and seconds. Since both are multiples of 60, this should work fine, except when the total hours exceeds 24, in which case Excel would want to roll over to another day after 24 hours, not 60. You can force Excel to interpret xx:yy as mm:ss by entering it in the form 0:xx:yy, if you're willing to do all that keying. If not, and if my presumption that Excel has assumed hh:mm for your mm:ss is correct, post back with some specific examples of what you're trying to do, and I might be able to suggest other workarounds. -- Earl Kiosterud www.smokeylake.com "Emily16" wrote in message ... I need to add a long list of minutes and seconds. (ex. 25:16, 16:39, 27:41) I have looked at all the functions and cannot find one that works. They all want to add the times assuming they are hours and minutes or time on a clock. I want to find a function that would add up the previous example and display it as 69:36 total minutes and seconds. It seems like one of the problems I am running into is finding or creating my own function that knows to add up the seconds to 60, not 100 like most decimal functions are used to. Does anyone have an answer? I would be forever grateful! |
#4
|
|||
|
|||
On Thu, 13 Oct 2005 12:11:05 -0700, "Emily16"
wrote: I need to add a long list of minutes and seconds. (ex. 25:16, 16:39, 27:41) I have looked at all the functions and cannot find one that works. They all want to add the times assuming they are hours and minutes or time on a clock. I want to find a function that would add up the previous example and display it as 69:36 total minutes and seconds. It seems like one of the problems I am running into is finding or creating my own function that knows to add up the seconds to 60, not 100 like most decimal functions are used to. Does anyone have an answer? I would be forever grateful! Well, you just add them up, and format them properly. The only issue I see is that, if the values are truly entered as you post, then Excel will be interpreting the input as hours:minutes rather than minutes:seconds. There are a variety of methods of dealing with this problem. If all the entries are in the same format, merely SUM them and divide by 60. Format the result either as [m]:ss or [h]:mm:ss The brackets around the h or the m keeps them from "rolling over" at 24 hrs or 60 minutes. For example, if your values are in column A: =SUM(A:A)/60 Format as one of my two suggestions. --ron |
#5
|
|||
|
|||
Enter the numbers with a 0 for the hour like so...
0:25:16 0:16:39 0:27:41 and use a custom format like so... [m]:ss you can them use a normal sum() function (or any arithmetic function) like so... =Sum(A1:A3) or =A1+A2+A3 and use the same custom format to get a result like... 25:16 16:39 27:41 69:36 "Emily16" wrote: I need to add a long list of minutes and seconds. (ex. 25:16, 16:39, 27:41) I have looked at all the functions and cannot find one that works. They all want to add the times assuming they are hours and minutes or time on a clock. I want to find a function that would add up the previous example and display it as 69:36 total minutes and seconds. It seems like one of the problems I am running into is finding or creating my own function that knows to add up the seconds to 60, not 100 like most decimal functions are used to. Does anyone have an answer? I would be forever grateful! |
#6
|
|||
|
|||
Emily16 Wrote: I need to add a long list of minutes and seconds. (ex. 25:16, 16:39, 27:41) I have looked at all the functions and cannot find one that works. They all want to add the times assuming they are hours and minutes or time on a clock. I want to find a function that would add up the previous example and display it as 69:36 total minutes and seconds. It seems like one of the problems I am running into is finding or creating my own function that knows to add up the seconds to 60, not 100 like most decimal functions are used to. Does anyone have an answer? I would be forever grateful! Hi Emily16 This site may help you http://www.cpearson.com/excel/datearith.htm Below is an extract: If you want to add up minutes and seconds, you must include a leading "0:" in your data. For example, enter "0:10:20" to indicate 10 minutes, 20 seconds. When you sum these times, Excel will display the sum in "time-of-day" format, meaning that adding 0:40:10 and 0:30:20 will yield 1:10:30. You can prevent Excel form "rolling over" at the hour by formatting the result cell as [m]:ss which will cause it to display 70:30 rather than 1:10:30. -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783 View this thread: http://www.excelforum.com/showthread...hreadid=475992 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting minutes and seconds to calculate a total average | Excel Worksheet Functions | |||
Sum minutes and seconds to total hours | Excel Discussion (Misc queries) | |||
Create a custom format to convert seconds to minutes. | Excel Discussion (Misc queries) | |||
Adding minutes to time | Excel Discussion (Misc queries) | |||
convert seconds to minutes and seconds | Excel Worksheet Functions |