QUERY with Week Number

Question

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?

Answer 1

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)

---

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