google sheets - query with date equality comparison not giving the same result as comparing using contains - Stack Overflow

admin管理员组

文章数量:1350953

I have the following 2 queries, which should give the same result but don't. In the example I am showing it is looking at data from another sheet and finding a match where Col 7 has a date of May 30, 2024. The first I used contains with the date in parentheses and it finds it. But the second, which should look for the date as well, finds no result. What am I missing? (I did go to the other sheet and put =isdate(G6) where G6 is the data for Col7 and it shows true)

=ifna(if(B3="",query(importrange("11EQ9lVbX6ioTECafiXTDxvoWRNDkEAEqj-KdI_-6BBB","'Responses'!AD3:AK")," select * where Col7 contains 'May 30, 2024' ",0),query(importrange("11EQ9lVbX6ioTECafiXTDxvoWRNDkEAEqj-KdI_-6BBB","'Responses'!AD:AL")," select Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8 where lower(Col9) contains '"&lower(B3)&"' ",0)),"No Results Found")

Finds one result

=ifna(if(B3="",query(importrange("11EQ9lVbX6ioTECafiXTDxvoWRNDkEAEqj-KdI_-6BBB","'Responses'!AD3:AK")," select * where Col7 = date '"&TEXT(DATEVALUE("May 30, 2024"),"yyyy-mm-dd")&"' ",0),query(importrange("11EQ9lVbX6ioTECafiXTDxvoWRNDkEAEqj-KdI_-6BBB","'Responses'!AD:AL")," select Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8 where lower(Col9) contains '"&lower(B3)&"' ",0)),"No Results Found")

No Result found (should find same result as first one)

I have the following 2 queries, which should give the same result but don't. In the example I am showing it is looking at data from another sheet and finding a match where Col 7 has a date of May 30, 2024. The first I used contains with the date in parentheses and it finds it. But the second, which should look for the date as well, finds no result. What am I missing? (I did go to the other sheet and put =isdate(G6) where G6 is the data for Col7 and it shows true)

=ifna(if(B3="",query(importrange("11EQ9lVbX6ioTECafiXTDxvoWRNDkEAEqj-KdI_-6BBB","'Responses'!AD3:AK")," select * where Col7 contains 'May 30, 2024' ",0),query(importrange("11EQ9lVbX6ioTECafiXTDxvoWRNDkEAEqj-KdI_-6BBB","'Responses'!AD:AL")," select Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8 where lower(Col9) contains '"&lower(B3)&"' ",0)),"No Results Found")

Finds one result

=ifna(if(B3="",query(importrange("11EQ9lVbX6ioTECafiXTDxvoWRNDkEAEqj-KdI_-6BBB","'Responses'!AD3:AK")," select * where Col7 = date '"&TEXT(DATEVALUE("May 30, 2024"),"yyyy-mm-dd")&"' ",0),query(importrange("11EQ9lVbX6ioTECafiXTDxvoWRNDkEAEqj-KdI_-6BBB","'Responses'!AD:AL")," select Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8 where lower(Col9) contains '"&lower(B3)&"' ",0)),"No Results Found")

No Result found (should find same result as first one)

• date
• google-sheets
• google-query-language

Share Improve this question Follow edited Apr 2 at 7:13 TheMaster 51.1k77 gold badges7272 silver badges9999 bronze badges asked Apr 2 at 4:11 DocDoc 2133 bronze badges 3

• 1 Would you be able to provide your sample sheet, with your initial output, and also your expected output so that we can further help you. You may use this to provide a markdown table (you may create one with the help of this link or an anonymous sample spreadsheet (using this link – Alma_Matters Commented Apr 2 at 4:58
• Try toDate(Col7)=date '"&TEXT(DATEVALUE("May 30, 2024"),"yyyy-mm-dd")&"' – TheMaster Commented Apr 2 at 7:11
• You can remove the comma (",") from "May 30, 2024" since you’re inputting the date manually. This will ensure that Col7 data will match 'May 30 2024' because you can't change Col7 yet, and it hasn't been processed yet. – Alma_Matters Commented Apr 2 at 13:26

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

The imported spreadsheet doesn't have a sheet named 'Responses'. The 'JGOOT Responses' tab looks like what is meant. In that tab, columns AI:AJ don't contain dates but text strings that just look like dates. The root cause is with the two text() formulas in 'RAW Data Corrected'!V1:W1.

You can convert those two columns into numeric dates like this:

=query(T1:T, "label Col1 'Formatted LEAVE date for email' format Col1 'mmm d, yyyy' ", 1)

You can confirm that the dates are numeric with the isnumber() function. Once they're numeric, you can do the import like this:

=let(
  data, importrange("11EQ9lVbX6ioTECafiXTDxvoWRNDkEAEqj-KdI_-6BBB", "JGOOT Responses!AD3:AL"),
  ifna(
    if(B3 = "", 
      query(data, "where toDate(Col7) = date '2024-05-30' ", 0),
      query(data, "where lower(Col9) contains '" & lower(B3) & "' ", 0)
    ),
    "No Results Found"
  )
)

The 'JGOOT Responses' tab has more than 50,000 blank rows at the bottom, which hurts performance. You should remove those blank rows, and edit the formulas in 'JGOOT Responses'!AM3:AN3 like this:

=arrayformula(iferror(datevalue(AI3:AI)))

To improve spreadsheet performance, see these optimization tips.

本文标签：