Results 1 to 5 of 5

20020313, 18:12 #1
 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.

20020313, 19:11 #2
 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

20020313, 19:18 #3
 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>

20020313, 19:52 #4
 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.

20020314, 02:13 #5
 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<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>