0

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.

1 Answer 1

4

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)))
2
  • like this? "select Col6 where Col6 = '"&$A95&"' and todate(Col11) = '"&TEXT($AR3,"yyyy-mm-dd")&"'" => error while parsing Mar 30, 2018 at 15:34
  • thanks for the hint regarding ;. No errors anymore, but still 0 instead of 2. This is the complete one: =COUNTA(IFERROR(query(IMPORTRANGE("1OHR0nr_w3oFNNxtMxRwv65xncCBRpK-vV3vvqmHGFPo"; "Sales March!A:K"); "select Col6 where Col6 = '"&$A95&"' and todate(Col11) = '"&TEXT($AR3;"yyyy-mm-dd")&"'"; 0))) Mar 30, 2018 at 15:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.