3

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")
3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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