Search code examples
arraysdatetimegoogle-sheetsfiltergoogle-query-language

Google Sheets QUERY function dealing with dates

I have a table (gDisplay) in my GoogleSheets file that looks like this:

enter image description here

So, in another table I can run this query without pain:

=QUERY(gDisplay!A2:I;"SELECT sum(F) WHERE A > '2021-09-01' ")

But I need to fetch last week results, and I was guessig that the query below could work:

=QUERY(gDisplay!A2:I;"SELECT sum(F) WHERE A > date '"&TEXT(TODAY()-8,"yyyy-mm-dd")&"' ")

But it's not working and I had no lucky searching for the answer. Any clue to fix this?

Solution

  • after 8 you need semicolon not comma:

    =QUERY(gDisplay!A2:I;
 "SELECT sum(F) 
  WHERE A > date '"&TEXT(TODAY()-8; "yyyy-mm-dd")&"'")

    but try:

    =INDEX(QUERY(gDisplay!A2:I*1; "SELECT sum(Col6) WHERE Col1 > "&DATEVALUE(TODAY()-8)))