Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Lounger
    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

  2. #2
    Lounger
    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.

  3. #3
    Uranium Lounger
    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

  4. #4
    Lounger
    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

  5. #5
    Platinum Lounger
    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 02-Jan-06 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 works
    Jerry

  6. #6
    Platinum Lounger
    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 dates
    Jerry

  7. #7
    WS Lounge VIP sdckapr's Avatar
    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

  8. #8
    WS Lounge VIP sdckapr's Avatar
    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 usenon-contiguous 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>


  9. #9
    Platinum Lounger
    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

  10. #10
    WS Lounge VIP sdckapr's Avatar
    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

  11. #11
    Lounger
    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

  12. #12
    WS Lounge VIP sdckapr's Avatar
    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
    alt-f11 (opens VB)
    Insert - module
    Copy the code you added to your worksheet and paste it into the macro pane on the right side
    alt-Q (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

  13. #13
    Lounger
    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.

  14. #14
    Platinum Lounger
    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

  15. #15
    Uranium Lounger
    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

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •