Google-sheets – Compare dates in query from IMPORTRANGE and cell

google sheetsgoogle-sheets-queryimportrange

for a few days I'm stuck at comparing two dates.

In a big table with dates as columns and products as rows, we want to display the amount of sales per day per product.

What the following query does:

Import our sales via IMPORTRANGE
query the specific sales for a given day and product ID
return 0 if there is an error (e.g. not found)
count the result

This is a working query with the date 2018-03-26 hard coded:

=COUNTA(IFERROR(query(IMPORTRANGE("1OHR0nr_w3oFNNxtMxRwv65xncCBRpK-vV3vvqmHGFPo"; "Sales March!A:K"); "select Col6 where Col6 = '"&$A95&"' and todate(Col11) = date '2018-03-26'"; 0)))

It shows how many sales a specific product has on the specified date.

Now I want the date from a cell in the sheet:

=COUNTA(IFERROR(query(IMPORTRANGE("1OHR0nr_w3oFNNxtMxRwv65xncCBRpK-vV3vvqmHGFPo"; "Sales March!A:K"); "select Col6 where Col6 = '"&$A95&"' and todate(Col11) = date '"&$AR3&"'"; 0)))

But this doesn't work. I tried A LOT of things for the past 3 days but I have no idea if I did that wrong (I'm a beginner) so I'm turning to you.
What might be wrong with the date comparison in this section?

"select Col6 where Col6 = '"&$A95&"' and todate(Col11) = date '"&$AR3&"'"

You can see it here in Column AR Row 95 but I can't give edit rights.

Best Answer

The date provided to QUERY must be:

a string of
format yyyy-mm-dd

Try changing from

=date '"&$AR3&"'"; 0)))

=date '"&TEXT($AR3;"yyyy-mm-dd")&"'"; 0)))

Related Solutions

Google-sheets – Using Query with IMPORTRANGE and comparison of dates

Several issues here.

When using query with importrange, the imported columns must be referred to as Col1, Col2, Col3, etc, according to their positions in the imported range. Your reference "ET" will not work.
You have a mess of quotation marks going on. Remember that the query string can contain single quotes. Instead of "WHERE ET>=date """&text(... it is easier to write "WHERE ET>=date '"&text(... because single quotes inside of double quotes need not be escaped.
You are missing a quotation mark in text(B1,yyyy-mm-dd").
The very end of query string is supposed to be an escaped quotation mark enclosed by quotation marks on both ends. Your """ is not enough for that. Save yourself headache and use "'", as advised by 2.
When a query is formed by concatenation like this, it is highly advisable to put it in a separate cell. E.g., your cell C1 can contain just the query string

="WHERE Col2>=date '"&text(A1,"yyyy-mm-dd")&"' and Col2<=date '"&text(B1,"yyyy-mm-dd")&"'"

and then some other cell has =query(importrange(...), C1). This approach simplifies debugging because you get to see what the query is before its attempted execution.

Google-sheets – Google Sheets Query group by multiple

Replace and by ,.

Final formula:

=QUERY(ARRAYFORMULA(IMPORTRANGE(
"https://docs.google.com/spreadsheets/d/1Wyewpz0j_IiKK8ESOapX5L4AedjvHccLZOOKS2tZGUA/",
"UsuallyAnotherSpreadsheetFile!B2:K")),"Select Col4, Col3, sum(Col8), sum(Col9) 
where (Col1 = '" & ClientInfo!$H$1 & "') group by Col4, Col3 label sum(Col8) '',
sum(Col9) ''")

Best Answer

Related Solutions

Google-sheets – Using Query with IMPORTRANGE and comparison of dates

Google-sheets – Google Sheets Query group by multiple

Related Topic