Google Sheets Query Syntax Where Between Two Dates That Are Variables-CodePudding

In Google Sheets, I am trying to query data from my 'ALL DATA' sheet that is between two dates.

The two dates are in B1 and B2 of my 'CALCS' sheet. Here is what I have tried so far in the CALCS sheet:

=QUERY('ALL DATA'!$A:$AW,"select C,F,K,AA,AK,AM where C=1 and AK >= date '"&B1&"' and AK <= date '"&B1&"'")

I am getting: "Unable to parse query string for Function QUERY parameter 2: Invalid date literal [44713]. Date literals should be of form yyyy-MM-dd."

However, all of my dates in 'ALL DATA'!AK and B1 and B2 are in that format. Is there a way to do this so I can change the dates in B1 and B2 and the query updates for those dates?

CodePudding user response：

replace AK >= date '"&B1&"' and AK <= date '"&B1&"' by

AK>=DATE'"&TEXT(B1;"yyyy-MM-dd")&"' AND AK<=DATE'"&TEXT(B2;"yyyy-MM-dd")&"'

CodePudding user response：

use:

=QUERY('ALL DATA'!$A:$AW,
 "select C,F,K,AA,AK,AM 
  where C=1 
    and AK >= date '"&TEXT(B1, "e-m-d")&"' 
    and AK <= date '"&TEXT(B2, "e-m-d")&"'")