Google-sheets – FILTER() Is not returning consistent results

google sheets

I'm having some issues with FILTER() not returning consistent results, below is an image detailing the inconsistencies.

enter image description here

When filtering for 3:30 PM I only get 2 results instead of 3. When filtering for 11:00 PM I get 4 results out of 4.

Why is this?

Edit: Turns out the numbers are not exactly the same:

enter image description here

However, the odd part is, those numbers are originally pulled from here:

enter image description here

This makes all of it even more confusing, whats going on here?

Best Answer

Timestamps are stored as floating point numbers in double precision, on the scale where 1 corresponds to a day. It is generally advisable not to test floating point numbers for equality; the rounding errors, inevitable in almost any calculation with them, may lead to unexpected results.

It appears that the transmission of time data from a form to the spreadsheet involved some sort of rounding of binary data along the way. Compare:

The number of zeros at the end of mantissa of the second number indicates that it was rounded along the way.

One solution is to use fuzzy comparison, for example

=filter(A76:A96, abs(A76:A96-B76) < 1e-9)

Since 10-9 days is less than 0.1ms, this matches the timestamps that are indistinguishable for practical purposes.

Another solution is to "fix" the distorted timestamp by converting the time to a string and back: 

=timevalue(text(A92, "hh:mm:ss"))

This will return the result identical to other 3:30 PM entries of your sheet.

Related Topic