inconsistency in COUNTIF of time values

[guardog]

Here's the simplified situation

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Time start[/TD]
[TD]Time End[/TD]
[TD]Duration[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]08:40 AM[/TD]
[TD]08:55 AM[/TD]
[TD]00:15[/TD]
[TD]=COUNTIF(C2:C3,"=0:15")[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]04:20 PM[/TD]
[TD]04:35 PM[/TD]
[TD]00:15[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

PROBLEM:
The countif returns only "1", this kinda puzzles me

I've tried applying *1440 to convert C2 and C3 into number of minutes (changed the Cell Formatting into General)

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Time start[/TD]
[TD]Time End[/TD]
[TD]Duration[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]08:40 AM[/TD]
[TD]08:55 AM[/TD]
[TD]15[/TD]
[TD]=COUNTIF(C2:C3,"=15")[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]04:20 PM[/TD]
[TD]04:35 PM[/TD]
[TD]15[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
PROBLEM:
The countif returns "0", err what?

Am I encountering some unknown limitation of countif here?

PURPOSE OF REFERRAL:
I am hoping to get the countif = 2 in the first situation

Many thanks in advance.

ADDITIONAL INFORMATION:
Using INT() in C2 and C3 after *1440 (situation 2) gives 14 and 15

 

[Asala42]

I've had to deal with this before. For some reason excel is not able to see the precise match on a specific timestamp after a subtraction. I think it has something to do with excel's limitation on significant figures.

The work around is to toss in a round statement. For example in C2:
=ROUND((B2-A2)*1440,0)

 

[Namestaken]

It is definitely a precision problem. When you converted to an integer with *1440, change the cell formatting to many decimal places to see this for yourself.

The above solution works or if you want something a little closer to your original formula, this will work (use Control + Shift + Enter to make it function):

=SUM(IF(MINUTE(C2:C3) = 15, 1, 0))

Alternatively, change your duration column to this and use your old COUNTIF() formula:

=TIME(HOUR(C3) - HOUR(B3), MINUTE(C3) - MINUTE(B3), 0)

Warning: This ignores seconds by default but can be modified to include them.

 

[Asala42]

You know on second thought, it's probably better to round to the nearest second instead of minute.

In C2, formatted as mm:ss
=ROUND((B2-A2)*86400,0)/86400

In D2:
=COUNTIF($C$2:$C$3,(15/1440))

 

[guardog]

Thank you guys for the input.

I used

[COLOR=#333333]=ROUND((B2-A2)*1440,0)[/COLOR]

Which is good enough for my data as it doesn't have seconds, at least for now there's no more odd 1 minute addition (or minus) when calculating straightforward duration such as in between 0800 and 0830
Will switch to the seconds conversion if odd results start to appear

Tried ROUNDUP((B2-A2)*1440,0) before this, which resulted in 1 minute addition (i.e. 31 minutes for 0800 to 0830)
ROUND seems to be better in this case.

CASE RESOLVED