Calculating total time

[odrisso]

Hi,

I have two columns like this way.

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]a[/TD]
[TD]102[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]135[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]157[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]189[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]201[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]222[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]245[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]290[/TD]
[/TR]
</tbody>[/TABLE]

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.

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD="width: 64, align: right"]
[/TD]
[TD="width: 64, align: center"]A
[/TD]
[TD="width: 64, align: center"]B
[/TD]
[TD="width: 64, align: center"]C
[/TD]
[TD="width: 64, align: center"]D
[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]1
[/TD]
[TD="width: 64"]
[/TD]
[TD="width: 64"]
[/TD]
[TD="width: 64, align: right"]a
[/TD]
[TD="width: 64, align: right"]b
[/TD]
[/TR]
[TR]
[TD="align: right"]2
[/TD]
[TD]total[/TD]
[TD]
[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]3
[/TD]
[TD="width: 64"]a[/TD]
[TD="width: 64, align: right"]102[/TD]
[TD="align: right"]-102[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]4
[/TD]
[TD="width: 64"]a[/TD]
[TD="width: 64, align: right"]135[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]5
[/TD]
[TD="width: 64"]a[/TD]
[TD="width: 64, align: right"]157[/TD]
[TD="align: right"]157[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]6
[/TD]
[TD="width: 64"]b[/TD]
[TD="width: 64, align: right"]189[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]-189
[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]7
[/TD]
[TD="width: 64"]b[/TD]
[TD="width: 64, align: right"]201[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]201
[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]8
[/TD]
[TD="width: 64"]a[/TD]
[TD="width: 64, align: right"]222[/TD]
[TD="align: right"]-222[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]9
[/TD]
[TD="width: 64"]a[/TD]
[TD="width: 64, align: right"]245[/TD]
[TD="align: right"]245[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]10
[/TD]
[TD="width: 64"]b[/TD]
[TD="width: 64, align: right"]290[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]0
[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="width: 341"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]SN[/TD]
[TD][/TD]
[TD]'Stage 2'[/TD]
[/TR]
[TR]
[TD]SN[/TD]
[TD][/TD]
[TD]'Stage 2'[/TD]
[/TR]
[TR]
[TD]Ex[/TD]
[TD][/TD]
[TD]'Stage 2'[/TD]
[/TR]
[TR]
[TD]SN[/TD]
[TD][/TD]
[TD]'Stage 2'[/TD]
[/TR]
[TR]
[TD]Ex[/TD]
[TD][/TD]
[TD]'Stage 2'[/TD]
[/TR]
[TR]
[TD]SN[/TD]
[TD][/TD]
[TD]'Stage 2'[/TD]
[/TR]
[TR]
[TD]SN[/TD]
[TD][/TD]
[TD]'Stage 2'[/TD]
[/TR]
[TR]
[TD]Ex[/TD]
[TD][/TD]
[TD]'Stage 2'[/TD]
[/TR]
[TR]
[TD]SN[/TD]
[TD][/TD]
[TD]'Stage 2'[/TD]
[/TR]
[TR]
[TD]Ex[/TD]
[TD][/TD]
[TD]'Stage 2'[/TD]
[/TR]
[TR]
[TD]SN[/TD]
[TD][/TD]
[TD]'Stage 2'[/TD]
[/TR]
[TR]
[TD]SN[/TD]
[TD][/TD]
[TD]'Stage 2'[/TD]
[/TR]
[TR]
[TD]SN[/TD]
[TD][/TD]
[TD]'Stage 2'[/TD]
[/TR]
</tbody>[/TABLE]

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.

[TABLE="class: cms_table_outer_border, width: 500"]
<tbody>[TR]
[TD]a[/TD]
[TD]102[/TD]
[TD]103[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]135[/TD]
[TD]136[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]157[/TD]
[TD]160[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]189[/TD]
[TD]191[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]201[/TD]
[TD]202[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]222[/TD]
[TD]223[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]245[/TD]
[TD]250[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]290[/TD]
[TD]292[/TD]
[/TR]
</tbody>[/TABLE]


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