Time Difference

[Maddy1234]

Hi experts,

I come with another problem.I have a dynamic set of data which updates according to the user input in another sheet. The data set looks like below.

	Time Started	Time Ended
Exercise 1	9:00	9:15
Exercise 2	9:15	10:15
Exercise 3	10:15	11:15
Exercise 4	11:15	12:15
Exercise 5	12:15	13:15
Exercise 6	13:15	14:15
Exercise 7	14:15	15:15
Exercise 8	15:15	16:15
Exercise 9	16:15	17:15
Exercise 10	17:15	18:15

<colgroup><col><col><col></colgroup><tbody>
</tbody>

What I want to achieve is the total elapsed time for all the exercises. So ideally I want to get the value MOD((C11-B2),1). But the problem I have is the data set is dynamic and I don't know how many rows Im gonna have so the C11 might change to something else.

Can you please educate me on how to retrieve the last valid row so I can get this to work?

Many thanks.

 

[Scott Huish]

=MOD(LOOKUP(9.99999999E+307,C:C)-B2,1)

Excel 2010
	A	B	C	D	E
1		Time Started	Time Ended
2	Exercise 1	9:00	9:15		9:15
3	Exercise 2	9:15	10:15
4	Exercise 3	10:15	11:15
5	Exercise 4	11:15	12:15
6	Exercise 5	12:15	13:15
7	Exercise 6	13:15	14:15
8	Exercise 7	14:15	15:15
9	Exercise 8	15:15	16:15
10	Exercise 9	16:15	17:15
11	Exercise 10	17:15	18:15
Sheet1

Cell Formulas
Range	Formula
E2	=MOD(LOOKUP(9.99999999E+307,C:C)-B2,1)

 

[Maddy1234]

Thanks mate works perfectly.

=MOD(LOOKUP(9.99999999E+307,C:C)-B2,1)

Excel 2010

	A	B	C	D	E
1		Time Started	Time Ended
2	Exercise 1	9:00	9:15		9:15
3	Exercise 2	9:15	10:15
4	Exercise 3	10:15	11:15
5	Exercise 4	11:15	12:15
6	Exercise 5	12:15	13:15
7	Exercise 6	13:15	14:15
8	Exercise 7	14:15	15:15
9	Exercise 8	15:15	16:15
10	Exercise 9	16:15	17:15
11	Exercise 10	17:15	18:15

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula E2 =MOD(LOOKUP(9.99999999E+307,C:C)-B2,1) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>