Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Apr 2001
    Location
    NOLA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculate and sum time from variable length rows? (Office 2000 SR1)

    Here's hoping a Woody's lounger can give me some pointers! A program runs against our database and builds an Excel spreadsheet showing buses down the A column and the time of day across Row1. The start time for each route appears under the appropriate hour (8:15 below the 8:00 column, for example) and the end time likewise (16:15 below 16:00). Calculating the elasped time for a single row is simple enough. However, each row may have a different start and stop cell. I need a column with a formula to calculate the elapsed time in each row and a sum of total time at the bottom. How can I find the start and stop cells that have time values from a row of mostly blank cells?

    Woody's loungers are the best.
    Thanks for any suggestions.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Calculate and sum time from variable length rows? (Office 2000 SR1)

    If your times are recorded in Columns B to say L, in row 2 would <pre> = MAX(B2:L2)-MIN(B2:L2)</pre>

    work ?.

    This calculates the elapsed time from earliest to latest times entered in the range, assuming the times are entered as time.

    Andrew C

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calculate and sum time from variable length rows? (Office 2000 SR1)

    Also, you'll want to format that column as time; otherwise the results will look weird.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    Lounger
    Join Date
    Apr 2001
    Location
    NOLA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculate and sum time from variable length rows? (Office 2000 SR1)

    WOW! That's great, I thought it would require some complex macro :>) To make things more challenging, some of my rows have blank time in the middle. For example, the bus is used from 06:00 until 2:00pm, the times are entered in the appropriate cells. At 5:00, usage resumes with a 17:00 entry and goes until 21:00. IS there any way to account for the 3 hours unused between 2 and 5?

    By the way, when formatting cells for time, why does the 37:30:55 format work while the 30:55.2 format produces weird results?

    Thank you so much for the quick formula, this is a big step forward.

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calculate and sum time from variable length rows? (Office 2000 SR1)

    Fortunately, we also have the LARGE function, so the formula you need is:
    <pre>=IF(COUNT(B2:J2)=2,LARGE(B2:J2,1)-LARGE(B2:J2,2),
    LARGE(B2:J2,1)-LARGE(B2:J2,2)+LARGE(B2:J2,3)-LARGE(B2:J2,4))</pre>

    The two lines are just a single formula. What this says is if there are two times, then subtract the second largest(ie the smallest) from the largest, otherwise (4 times) subtract the second largest from the largest and add this to the difference between the third and fourth largest. I've attached a sample workbook. HTH --Sam
    Attached Files Attached Files
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Posting Permissions

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