Google Sheets – Query Select All Values of Specific Month

formulasgoogle sheetsgoogle-sheets-datesgoogle-sheets-query

A have a table where Column A has dates. I want to get all values of a specific month

I have the following query for May for example

=query(Database!A1:AD,"Select A,E,F,U,V,W,X,AB,AC where Month(A) = 5",1)

but it does not work. Where I am wrong?
Query

=query(Database!A1:AD,"Select A,E,F,U,V,W,X,AB,AC where A > date '2019-05-01' and A < date '2019-05-31'",1)

works fine but I have a separate drop-down list with the months' number 01 02 03 …. and I want the query to change when I change the month number. But even when I put the month number directly into query it shows nothing. No any mistake – just no rows with values, only header

Best Answer

months in QUERY starts from 0 so you need to fix it with +1

=QUERY(Database!A1:AD, "select A,E,F,U,V,W,X,AB,AC where month(A)+1 = 5", 1)

Related Solutions

Google Sheets Query – How to Reference a Cell Containing a Date

Indeed, one cannot put things like "A6" inside the query string because then it would just a part of a string, not a cell reference. Instead, concatenate the value of the cell to the string:

=JOIN(", ", UNIQUE(QUERY(A2:F4, "select E where day(A) ="&day(A6))))

You don't need transpose, because join works equally well with one-dimensional arrays of either direction: vertical or horizontal.

That said, I find a solution using filter slightly more readable:

=join(", ", unique(filter(E$2:E$4, A$2:A$4 = A6)))

Google Sheets Query – Specific Values Based on Date Ranges

Grouping

In general, aggregation functions such as SUM or COUNT are used in the presence of GROUP BY: one specifies how to group the rows so that summation or counting is executed within each group. You don't have any GROUP BY. It would make sense to group by E, so that you count each client id once.

Date format

When dates are used in a query, they have to appear like date 'yyyy-MM-dd', for example

where A >= date '2017-05-01' and A <= date '2017-05-21'

Creating such a string from dynamic dates (e.g., 60 days ago, =TODAY()-60 involves conversion to text using text and an annoying amount of concatenation:

"... where A >= date '" & text(today()-90, "yyyy-MM-dd") & "' and ..."

If you go this route, it is advisable to form the query string in its own cell so you can see what it is. query can take the query string by cell reference.

Alternative: filter

It's much easier to use filter for the task you described. Here is the count of unique values in E2:E where A entry is between 30 and 90 days ago:

=countunique(iferror(filter(E2:E, A2:A >= today()-90, A2:A <= today()-30)))

The wrapper iferror is included to suppress "#N/A" that filter returns when there are no matches. (As pointed out by ocWavean).