Google-sheets – QUERY with Week Number

filterformulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-query

I use query to extract two columns from another sheet with data.

  • Column A has dates
  • Column B – numbers

I want to extract only rows that correspond to a specific calendar week number. For example 19.
I use this query

=query(Database!A1:AD,"Select A,AB where WEEKNUM(A)=19",1)

But it returns #VALUE error. What is wrong?

Best Answer

weeknum is not a valid scalar function so you need to trick it like this: 

=ARRAY_CONSTRAIN(ARRAYFORMULA(QUERY({A2:B, WEEKNUM(A2:A)}, "where Col3 = 21")), 999^99, 2)

0

=FILTER(A2:B, WEEKNUM(A2:A)=21)

0

Related Topic