Results 1 to 15 of 16

20060101, 18:11 #1
 Join Date
 Dec 2005
 Posts
 37
 Thanks
 7
 Thanked 0 Times in 0 Posts
Summing Transit Times (Excel 2003 SP1)
Hello,
I am looking for suggestions.
I have twelve separate categories, each of which I am tracking multiple transit times in individual rows through out the year by subtracting the arrival date and time from the departure date and time in a dd:hh:mm format for each occurrence. I am trying to come up with a way to sum the transit time of all of the occurrences of each category together to come up with a total annual transit time for each.
There is not a pattern to the transit times of each category as the year progresses meaning one category could have 30 occurrences in a year while another could only have two occurrences

20060101, 19:54 #2
 Join Date
 Dec 2005
 Posts
 37
 Thanks
 7
 Thanked 0 Times in 0 Posts
Re: Summing Transit Times (Excel 2003 SP1)
I believe I have discovered one issue. As the total time accumulates past 31 days, the total time will start over again. I have changed the format from dd:hh:mm to mm:dd:hh:mm and I now see the month equivalent to the 31 days. The problem now is the total transit time is always showing an extra month. I am assuming this is because it is is designed to show a current month rather than the number of months of accumulated time. I tried to subtract 1 month using the value date(0,1,0), which works until the total transit time rolls past 31 days and displays a "2" in the month place holder. There has to be an easier way.

20060101, 20:33 #3
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Summing Transit Times (Excel 2003 SP1)
There are a number of easier ways, depending on exactly what result you want. First, there is no need to use the IF statement to convert the string labels to numbers. You can use a SUMIF statement like this:
<code>
=SUMIF(A7:A259,"car #1",G7:G259)
</code>
Excel keeps Date/Time values as the number of days and fractional parts of days since 12/31/1899. Therefore, when you add up Date/Time values, that is what you get, If you format that as "dd hh:mm", the dd part will be the day number in the month represented by that date/time. If you are working with elapsed time, as you are when you add transit times, then you can format the result as [hh]:mm and you will get the result as elapsed hours and minutes and the result can be more than 24 hours. If you really want the result in days, hours, and minutes, then you can use some arithimetic and the TEXT function to generate a string that displays what you want. However, you will not be able to use that result in further calculations. If that is what you want, give us a better description of exactly what you are looking for and we will help with the formula.Legare Coleman

20060102, 19:06 #4
 Join Date
 Dec 2005
 Posts
 37
 Thanks
 7
 Thanked 0 Times in 0 Posts
Re: Summing Transit Times (Excel 2003 SP1)
Thank you Legare,
Good point. I will use the category column for my SUMIF statement. As for the determining the individual and total transit times for each category, I would like to know the amount of hours for each instance and what this equates to in days:hours:minutes. Then I would like to add all of the times for each category as total number of hours and what this equates to in days: hours: minutes.
I believe I have the calculations down for do this in the attached spreadsheet. I would rather have the days hours and minutes together in one cell as a time format (dd:hh:mm) if possible. Also, if there is a better way than what I have in the spreadsheet, I would appreciate an example of it.
Thank you again Legare

20060102, 20:43 #5
 Join Date
 Feb 2002
 Location
 A Magic Forest in Deepest, Darkest Kent
 Posts
 5,681
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Summing Transit Times (Excel 2003 SP1)
<P ID="edit" class=small>(Edited by Jezza on 02Jan06 21:43. Slight mistake in formula)</P>Hi there
If you are ready for quite a hefty formula, try this in I18:
=(INT(SUM(C3,C11)/24)& ":" & INT(SUM(C3,C11)(24*INT(SUM(C3,C11)/24)))& ":"&INT((SUM(C3,C11)INT(SUM(C3,C11)))*60))
I am sure someone will come up with a tidier one but this worksJerry

20060102, 20:49 #6
 Join Date
 Feb 2002
 Location
 A Magic Forest in Deepest, Darkest Kent
 Posts
 5,681
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Summing Transit Times (Excel 2003 SP1)
....and if you want a VB module:
Function TimeConv(theTime As Range)
TimeConv = (Int(Application.WorksheetFunction.Sum(theTime) / 24) & ":" & Int(Application.WorksheetFunction.Sum(theTime)  (24 * Int(Application.WorksheetFunction.Sum(theTime) / 24))) & ":" & Int((Application.WorksheetFunction.Sum(theTime)  _
Int(Application.WorksheetFunction.Sum(theTime))) * 60))
End Function
and use the function as follows:
=TimeConv(B2:B3)
and it will format your sums of datesJerry

20060102, 21:06 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Summing Transit Times (Excel 2003 SP1)
How about this as an improvement over Jezza's formula:
=INT(SUM(C3,C11)/24)&":"&TEXT(MOD(SUM(C3,C11)/24,1),"hh:mm")
Note both this and Jezza's formulas are text and not a true number so it can not be used in calculations. If you need the number in a calc, you should keep the total in a separate cell from the "display"
Steve

20060102, 21:20 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Summing Transit Times (Excel 2003 SP1)
This function is shorter than Jezza' s is and it allows you to usenoncontiguous ranges:
=TimeConv(C3,C11)
Steve
<pre>Option Explicit
Function TimeConv(ParamArray theTime())
Dim dSum As Double
dSum = Application.WorksheetFunction.Sum(theTime) / 24
TimeConv = Int(dSum) & ":"
TimeConv = TimeConv & Format(dSum  Int(dSum), "hh:mm")
End Function</pre>

20060102, 21:26 #9
 Join Date
 Feb 2002
 Location
 A Magic Forest in Deepest, Darkest Kent
 Posts
 5,681
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Summing Transit Times (Excel 2003 SP1)
Yes, nice one Steve. I have noted the hh:mm part of your equation. Thanks
Jerry

20060102, 22:25 #10
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Summing Transit Times (Excel 2003 SP1)
Using Text (XL) /Format (VB) is a much simpler "time converter" than doing all the logic you were coding.
It also works for dates as well as other formatting.
Steve

20060103, 16:04 #11
 Join Date
 Dec 2005
 Posts
 37
 Thanks
 7
 Thanked 0 Times in 0 Posts
Re: Summing Transit Times (Excel 2003 SP1)
Thank you Steve and Jezza,
I have copied both codes above to my work sheet to look at them and the format is just what I am looking for.
Steve,
I also tried looking at the results of the code =TimeConv(C3,C11) after I copied it to my work sheet but it only displays a "#Name?". I do not see this particular function in my list on Excel to get an explanation of its use. Will you help me out with this?
I am new to these types of forums so I am also not sure of who you are the message and what you are describing in your last post 546,121. If it is directed to me, will you please explain?
Thank you for your help

20060103, 16:13 #12
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Summing Transit Times (Excel 2003 SP1)
For TimeConv to work it must be added to module in the workbook
altf11 (opens VB)
Insert  module
Copy the code you added to your worksheet and paste it into the macro pane on the right side
altQ (closes VB)
<post#=546121>post 546121</post#> was a comment to Jezza. It was my comment that the code he created to do the some of the conversion is "builtin" to the Text/Format functions. Using them can save him a lot of work in coding.
Steve

20060103, 20:23 #13
 Join Date
 Dec 2005
 Posts
 37
 Thanks
 7
 Thanked 0 Times in 0 Posts
Re: Summing Transit Times (Excel 2003 SP1)
Hi Steve,
I am still having trouble. More than likely because I have not been in VB before. I can open VB, select the worksheet, insert the script :
Option ExplicitFunction TimeConv(ParamArray theTime()) Dim dSum As Double dSum = Application.WorksheetFunction.Sum(theTime) / 24 TimeConv = Int(dSum) & ":" TimeConv = TimeConv & Format(dSum  Int(dSum), "hh:mm")End Function
into a module, then close VB and copy =TimeConv(C3,C11) to a cell in the worksheet but I still get a "#Name?"
I must be missing some steps.

20060103, 21:26 #14
 Join Date
 Feb 2002
 Location
 A Magic Forest in Deepest, Darkest Kent
 Posts
 5,681
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Summing Transit Times (Excel 2003 SP1)
Hi there
I have copied Steve's code into a module into the attached workbook.
Open it, press Alt+F11 and click Module1 in the left hand pane, you will see the function he wrote in the right hand pane.Jerry

20060104, 01:14 #15
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Summing Transit Times (Excel 2003 SP1)
If by "select the worksheet," you mean that you are putting the code into the module behind the worksheet, then that is the problem. If you are double clicking on the worksheet in the project explorer, or are right clicking on it and selecting "View Code," then you are putting the code in the wrong place. These modules are used for event routines. You must place that code in a normal module (a module that you get when you select Module from the Insert menu in the VB Editor. A normal module will show up as a module under the Modules collection in the Project Explorer.
Legare Coleman