Time / counting

[handysmurf]

Two questions ... is there a formula that will count conditionally? In other words count the number of times the code "j" appears in a column.

Second ... I have one column that subtract time and the result is in minutes in this format "00:07:23" I need this as a decimal by either altering the existing formula or by another column. The present formula simply subtracts the begin from the end.

 

[oldbrewer]

=countif(a1:a10,"j") counts the j's

do you want 7:23 to be 7.38 minutes

 

[handysmurf]

thank you and yes to the question

 

[oldbrewer]

[TABLE="width: 809"]
<colgroup><col><col span="11"></colgroup><tbody>[TR]
[TD="align: right"]00:07:23[/TD]
[TD][/TD]
[TD="align: right"]07.38[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 8"]=VALUE(MID(TEXT(A1,"hh:mm:ss"),4,2))+VALUE(RIGHT(TEXT(A1,"hh:mm:ss"),2))/60[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

a​

[/TD]
[TD]

b​

[/TD]
[TD]

c​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]00:00:7:23[/TD]
[TD]

7:23​

[/TD]
[TD]

7.38​

[/TD]
[/TR]
</tbody>[/TABLE]


b1=RIGHT(A1,4)+0 custom format column b h:mm

c1=b1*24 format column c as number with two decimal

 

[handysmurf]

Excellent .... thanks!