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.

[TABLE="width: 297"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]


[/TD]
[TD]Time Started[/TD]
[TD]Time Ended[/TD]
[/TR]
[TR]
[TD]Exercise 1[/TD]
[TD="align: right"]9:00[/TD]
[TD="align: right"]9:15[/TD]
[/TR]
[TR]
[TD]Exercise 2[/TD]
[TD="align: right"]9:15[/TD]
[TD="align: right"]10:15[/TD]
[/TR]
[TR]
[TD]Exercise 3[/TD]
[TD="align: right"]10:15[/TD]
[TD="align: right"]11:15[/TD]
[/TR]
[TR]
[TD]Exercise 4[/TD]
[TD="align: right"]11:15[/TD]
[TD="align: right"]12:15[/TD]
[/TR]
[TR]
[TD]Exercise 5[/TD]
[TD="align: right"]12:15[/TD]
[TD="align: right"]13:15[/TD]
[/TR]
[TR]
[TD]Exercise 6[/TD]
[TD="align: right"]13:15[/TD]
[TD="align: right"]14:15[/TD]
[/TR]
[TR]
[TD]Exercise 7[/TD]
[TD="align: right"]14:15[/TD]
[TD="align: right"]15:15[/TD]
[/TR]
[TR]
[TD]Exercise 8[/TD]
[TD="align: right"]15:15[/TD]
[TD="align: right"]16:15[/TD]
[/TR]
[TR]
[TD]Exercise 9[/TD]
[TD="align: right"]16:15[/TD]
[TD="align: right"]17:15[/TD]
[/TR]
[TR]
[TD]Exercise 10[/TD]
[TD="align: right"]17:15[/TD]
[TD="align: right"]18:15[/TD]
[/TR]
</tbody>[/TABLE]

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
Time Started	Time Ended
Exercise 1
Exercise 2
Exercise 3
Exercise 4
Exercise 5
Exercise 6
Exercise 7
Exercise 8
Exercise 9
Exercise 10

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]9:00[/TD]
[TD="align: right"]9:15[/TD]
[TD="align: right"][/TD]
[TD="align: right"]9:15[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]9:15[/TD]
[TD="align: right"]10:15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]10:15[/TD]
[TD="align: right"]11:15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]11:15[/TD]
[TD="align: right"]12:15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]12:15[/TD]
[TD="align: right"]13:15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]13:15[/TD]
[TD="align: right"]14:15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]14:15[/TD]
[TD="align: right"]15:15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]15:15[/TD]
[TD="align: right"]16:15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]16:15[/TD]
[TD="align: right"]17:15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]17:15[/TD]
[TD="align: right"]18:15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=MOD(LOOKUP(9.99999999E+307,C:C)-B2,1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]