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.