Calculating total time

[odrisso]

Hi,

I have two columns like this way.

a	102
a	135
a	157
b	189
b	201
a	222
a	245
b	290

<tbody>
</tbody>

Here the values are in seconds. I want to write a formula, which show the total duration of seconds of a and b.

For example: here total duration of a is = (157-102) + (245-222) = 78 seconds.

Please suggest me how to do it.

Thanks

 

[joeu2004]

I want to write a formula, which show the total duration of seconds of a and b. For example: here total duration of a is = (157-102) + (245-222) = 78 seconds.

I would structure a table as follows.

	A	B	C	D
1			a	b
2	total		78	12
3	a	102	-102
4	a	135	0
5	a	157	157
6	b	189		-189
7	b	201		201
8	a	222	-222
9	a	245	245
10	b	290		0

<tbody>
</tbody>

Formulas:
C2: =SUM(C3:C10)
C3: =IF($A3=C$1,IF($A3<>$A2,-$B3*($A3=$A4),IF($A3<>$A4,$B3,0)),"")

Copy C2 and paste into D2.

Copy C3 and paste into C3:D10

Note that D10 is zero because there is no end-time to subtract B10 from. I presume that is what you want, based on your example.

If not, explain how the duration should be calculated in that case, if you need help to modify the formula column D.

 

[odrisso]

Hi, yess..Thanks a lot. It works..

I have Another query. I have the following table:

SN		'Stage 2'
SN		'Stage 2'
Ex		'Stage 2'
SN		'Stage 2'
Ex		'Stage 2'
SN		'Stage 2'
SN		'Stage 2'
Ex		'Stage 2'
SN		'Stage 2'
Ex		'Stage 2'
SN		'Stage 2'
SN		'Stage 2'
SN		'Stage 2'

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

Now, I want to delete the 'Stage 2' from 2nd coloumn where there is an Ex.

Please kindly let me know how to do it. Thanks again

 

[odrisso]

I made a mistake..

I have three columns like this way.

a	102	103
a	135	136
a	157	160
b	189	191
b	201	202
a	222	223
a	245	250
b	290	292

<tbody>
</tbody>

Here the values are in seconds. I want to write a formula, which show the total duration of seconds of a and b.

For example: here total duration of a is = (160-102) + (250-222) = 86 seconds.

Please suggest me how to do it.

Thanks