0

I found this BUG while trying to use MATCH using Queried data against the actual data.

This Google Spreadsheet shows the BUG in action.

When you format the data as Number the problem is fixed. This suggests that the QUERY has a problem with dates.

Workaround use the FILTER function instead of QUERY. (Also shown in the sample spreadsheet)

Image showing the error

migration rejected from superuser.com Mar 20 at 15:49

This question came from our site for computer enthusiasts and power users. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

put on hold as unclear what you're asking by user0, ale, serenesat, Vidar S. Ramdal, OnenOnlyWalter Mar 20 at 15:49

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

2

The bug – and it is indeed a bug, I would classify it as a design but on the part of GS developers – stems from the fact that internally, in GS, datetimes are kept as numbers, and what's more, as floating-point numbers. The integer part represents the number of days since Jan 1, 1970 while the fractional parts represents the fraction of the day and is converted to seconds.

As any developer knows (but perhaps spreadsheet users should not be expected to know that, eh Googlers?) you cannot compare floating-point numbers using simple equality, because their representation may vary even if they look the same at approximation. It becomes clear when you view a mismatching date as a number, at big enough precision:

enter image description here

The way to do comparison on FP numbers is to see if the difference between them is less than the precision you are interested in - for sub-second precision, in this case the formula should look something like this:

= ABS(A5-D5) < 0.0000001
  • Thank you. It's funny (and weird) that you managed to view the differences when formatting as Number with enough precision. When I format them as Numbers even adding lots of precision they become equal. I updated my sample sheet to include this. – Luciano Mar 16 at 22:15
  • ah, I managed to get the wrong Number for the offending date by formatting only the QUERIED date as Number with greater precision. Thanks. Updated the sample sheet. – Luciano Mar 16 at 22:18
1
  • it's more likely due to limited flexibility of QUERY formula
  • the 2nd row can be fixed simply by removing header row:

    =QUERY(A2:B;"select A where B = ''")

    0


  • and for the 5th row... QUERY doesn't quite well understand timestamp values (in this scenario, timevalue decimal is wrongly rounded by the query - interestingly this happens only on 27th second) so column A needs to be priorly formatted as Number or you can do text comparsion:

    =ARRAYFORMULA(QUERY(TO_TEXT(A2:A);"select Col1"))
    =TO_TEXT($A5)=D5

    4

  • Thank you. I updated the image and description to remove the false-positive introduced by wrongly keeping the header in the query function. But this definitely is a bug on the QUERY function when dealing with dates. Your workaround of using text comparison is not recommended because dates might be formatted differently. – Luciano Mar 16 at 22:11
  • 1

Not the answer you're looking for? Browse other questions tagged or ask your own question.