Formula Sumproduct with Subtotal Time count

[johnster]

I've found a formule for counting the time (only the visible filtered cells) but there is stil an issue.

When i filter the column from 20:00 till 21:00 is see 4 values. The result of my formula give me 5 values.

"

Formula: =SUMPRODUCT(($N$3:$N$9992>=S$1)*($N$3:$N$9992<T$1)*SUBTOTAL(2;OFFSET($N$3;ROW($N$3:$N$9992)-ROW($N$3);)))

Thx in advance

 

[jasonb75]

I can see no obvious reason for it to do that.

Try changing the time in T1 to 20:59:59, If that corrects the result then it would suggest that floating point precision is the cause.

I get the correct result with the visible data from your screen capture but have seen this happen once previously with european regional settings.

 

[johnster]

I can see no obvious reason for it to do that.

Try changing the time in T1 to 20:59:59, If that corrects the result then it would suggest that floating point precision is the cause.

I get the correct result with the visible data from your screen capture but have seen this happen once previously with european regional settings.

It works, thanks so much.

 

[jasonb75]

I would advise caution. You say that it works, but that depends on additional data.
If your timestamps are to the second then it might work with that one, but fail later with another.

One thing to remember with date and time in excel is that it doesn't lose precision with formatting. 21:00:59 still retains the 59 seconds if you change the format to only show hours and minutes. Although you can not see the seconds, a formula still can and it will still use them.