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?
2 Answers
replace AK >= date '"&B1&"' and AK <= date '"&B1&"' by
AK>=DATE'"&TEXT(B1;"yyyy-MM-dd")&"' AND AK<=DATE'"&TEXT(B2;"yyyy-MM-dd")&"'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")&"'")
