Search code examples
google-sheetsgoogle-query-language

Google Sheets QUERY returning wrong values using >date

Note: Australian date format (DD/MM/YYYY) below.

I have the following formula on Sheet2:

=QUERY ('Sheet1'!$A:$B, "select B where (A>date'"&text ($C$3, "yyyy-MM-dd")&"')", 0)

The source data on Sheet1 ($A:$B) looks like:

  • 26/05/2015 A1
  • 26/05/2015 A2
  • 26/05/2015 A3
  • 10/11/2015 A4
  • 02/01/2015 A5
  • 21/07/2015 A6
  • 24/12/2015 A7
  • 24/12/2015 A8
  • 25/12/2015 A9
  • 27/08/2015 A10

The value stored in $C$3 on sheet2 is manually entered, but is currently set to "01/12/2015".

When I run the query it returns me with these values in my data set

  • 10/11/2015
  • 02/01/2015
  • 21/07/2015
  • 24/12/2015
  • 24/12/2015
  • 25/12/2015
  • 27/08/2015

Why is the greater than not working for my dates?

e.g. I'm asking the query to give me all dates that occur after the 01/12/2015, but am receiving dates before then (even if the dates were doing something funny with the AU/US date format, it still doesn't make sense).

Solution

  • A FILTER formula may work instead:

    =FILTER(Sheet1!B:B,Sheet1!A:A>C3)