Results 1 to 11 of 11
  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Adding Elapsed Times (2002)

    I need to add a column of elapsed times, to find the total. The column looks like this, in mm:ss format:

    45:14
    00:18
    02:35
    etc.

    I need the total, which in this case would be 48:07.

    The total could be several hours, so it would surely require hh:mm:ss format. There's a chance that the total could exceed 24:59:59 -- I could live with it rolling over to 00:00:00, but I'd be willing to work pretty hard to avoid it.

    I've dabbled in Excel Dates and Times, but never really understood them. (Up until now, I've been able to do what I need without knowing too much about underlying principles.)

    Any help will be genuinely appreciated.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #2
    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: Adding Elapsed Times (2002)

    Use the "elapsed time" custom format

    [h]:mm:ss which will go larger than 24 for elapsed time in HOURS

    or [m]:ss for elapsed time in MINUTES (will go larger than 60)

    h:mm:ss is for "time of day so can NOT be larger than 23:59:59 since after the next second it is 0:00:00 of the next day.

    Steve

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Elapsed Times (2002)

    See Steve's message on how to format the cell to get elapsed times. However, he didn't mention that you can just add the times using the "+" operator or the SUM() function. It was not clear from your message that you knew that.
    Legare Coleman

  4. #4
    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: Adding Elapsed Times (2002)

    Legare,
    Good point.
    I assumed that Lou knew this, but maybe it wasn't clear to him.

    Also to get the individual elapsed times, you can subtract the end time - start time to get the elapsed time. If they occur across days, just include the date and time together.
    Steve

  5. #5
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Re: Adding Elapsed Times (2002)

    Thanks advice from both of you, I'm on the right track. But I'm still a little short of the goal.

    The elapsed times are imported from a text file. As long as the text file expresses an elapsed time of ten seconds as 00:00:10 or 0:00:10, Excel takes care of formatting the column properly, and I'm able to add the elapsed times by creating a single "total" cell. If I use the [h]:mm:ss cell format for my "total" cell, the hours don't roll over at 24:59:59. (And the formatting may even happen automatically when I set up the cell -- I don't remember exactly. In any case, it's easy).

    My difficulty is that, in the original text file, it's best not to include the hours for times under an hour (to enhance readability when the text file is viewed by things other than Excel). So I'd like to format ten minutes as 10:00, rather than 0:10:00, in the text file.

    How can I get Excel to recognize 10:00 as ten minutes, and not ten hours? (I've tried a lot of different ways, including [m]:ss, all without success).
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  6. #6
    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: Adding Elapsed Times (2002)

    If the values are being imported as TEXT, you can use this formula to convert the "minute text" to excel minutes (assuming value is in A1):
    =VALUE("0:"&A1)
    format as "[m]:ss"

    If the values are being imported as numbers/time and excel is assuming they are hr:min then use this formula to convert (assuming value is in A1)
    =A1/60
    format as "[m]:ss".
    Steve

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Elapsed Times (2002)

    Are you saying that you plan to have a mixture of values formatted as 10:00:00 and 10:00 where the first is 10 hours and the second is 10 minutes. If that is what you are saying, I think that you are going to have problems since I think that Excel is going to interpret both of those as 10 hours and you will have no way of knowing which is which. You could write VBA code to import the data and handle that yourself. A second way would be to import the data as text and then use either VBA or formula to convert the text to time. If you have a mixture, the formula is going to be more complicated than Steve's formula since it will have to use an IF statement to figure out which is which and do the conversions differently. It might look something like:

    <pre>=IF(LEN(A1)>5,VALUE(A1),VALUE("00:"&A1))
    </pre>

    Legare Coleman

  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: Adding Elapsed Times (2002)

    Once he converts his numbers to hours or minutes to get them to the correct value, he could format on the sheet some with "[h]:mm:ss" to be hours ("10:00:00") and others with "[m]:ss" to be minutes ("10:00") and even others with "[h]:mm" to be hours ("10:00") if desired.

    Excel will NOT have any problems keeping the numbers straight, since the underlyiing numeric values are correct. The displayed format is more likely to confuse the USER since it might not be clear when it is hours and when it is minutes.

    Steve

  9. #9
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Adding Elapsed Times (2002)

    Hi Lou,

    If your times are imported as text strings instead of time values (into, say, A1:A20), you can aggregate them by entering:
    =SUM(TIMEVALUE(IF(LEN(A1:A20)<7,"00:"&A1:A20,A1:A2 0)))
    as an array formula (ie ctrl-shft-enter instead of enter)

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  10. #10
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Re: Adding Elapsed Times (2002)

    Thanks to all of you for your help. At this point, I can get the results I need, regardless of how I decide to format the times in the text file. I couldn't have done it without you! (Or more accurately, I would have spent a lot of time and mental energy wrestling with a problem for which I wasn't sure there was an answer.)

    I'm thinking that I'm going to have the text file put the time in 00:00:00 format, and just let the users disregard the leading zeroes. Then their work will be easier if they decide to import the data into Excel.

    The project this is for, by the way, is a small on-screen counter, programmed in Visual Basic: click the button, register a count. There are provisions for logging counts and totals to a text file, and that's where the possible use of Excel arises.

    I use the counter and its log to keep track of the number of Solitaire games I play between wins (Average = 4.5). The timekeeping aspect is to let me keep track of how much of my precious life I'm spending on Solitaire.

    I'm hoping that my program will be helpful to other Solitaire addicts.

    On behalf of the addict community, I thank you again for your help! <sheepish grin>
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Elapsed Times (2002)

    Once they are converted, you are absolutely correct. My problem is what Excel is going to do when it does the conversion if the values are not imported as text. Excel will convert 10:00 to ten hours zero minutes and zero seconds, not zero hours ten minutes and zero seconds.
    Legare Coleman

Posting Permissions

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