Compare dates in query from IMPORTRANGE and cell

Question

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.

Answer 1

The date provided to QUERY must be:

• a string of
• format yyyy-mm-dd

Try changing from

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

to

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