Construct query where condition is in the past week?

Using Google Sheets' `=QUERY` function, is it possible to construct a `select` such as follows?

``````=QUERY('My Sheet'!B:D,"select A where C = In the Past Week")
``````

I'll give answers for two version of the question.

Within the last 7 days

``````"select A
where C > date '" & text(today()-7, "yyyy-mm-dd")
& "' and C <= date '" & text(today(), "yyyy-mm-dd") & "'"
``````

For example, today this would result in the query string

``````select A where C > date '2017-03-07' and C <= date '2017-03-14'
``````

However, with `filter` the same result is achieved without a messy syntax:

``````=filter(A:A, C:C > today()-7, C:C <= today())
``````

Within a given week

On way is to use the built-in function `weeknum` for this purpose. It has to be applied before `query` runs, as spreadsheet functions are not a part of query syntax.

``````=arrayformula(query({A2:C, weeknum(C2:C)}, "select Col1 where Col4 = " & weeknum(today())))
``````

Here, `{A2:C, weeknum(C2:C)}` is an array with 4 columns, the last one being the week number of the data in column C. The query selects column 1 (A) where column 4 matches today's week number. Notice that references Col1, Col2, etc are used when querying an array constructed with `{}`.

The formula may have issues around New Year Day (when a week is split between years) but with any luck you'll be on vacation then...

Again, the `filter` approach is easier:

``````=filter(A2:A, weeknum(C2:C) = weeknum(today()))
``````
• This is brilliant. Thank you so much! Query is powerful. :-) – orschiro Mar 14 '17 at 7:08
• I tried to make use of your second formula: `=arrayformula(query({Payments!J2:U, weeknum(U2:U)}, "select Col1 where Col6 = " & weeknum(today())))` How can I handle the following error? `Function ARRAY_ROW parameter 2 has mismatched row size. Expected: 1346. Actual: 1500.` – orschiro Mar 14 '17 at 10:00
• The reference u2:u is missing the sheet name. – user135384 Mar 14 '17 at 11:33
• Also, when there is no aggregation, `filter` is more appropriate - it does exactly what the name suggests, with fewer worries about syntax. – user135384 Mar 14 '17 at 11:36
• Thanks for the `filter` hint! – orschiro Mar 14 '17 at 19:44