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

1. ## 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. ## 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. ## 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.

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

#### Posting Permissions

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