SIMPLE DATE QUERY

[DEBCal]

Hi

I'm trying to create a monthly query of data. I would like to return ALL MATCHES within a date range e.g. all dates in June even if there are multiple entries with the same date.

I'm trying two different approaches and failing at both.

The "=QUERY" seems to be almost functional. I can return a list of dates by using =QUERY(Data!$A$2:$F$20,"select A",-1). However if the month changes my results are not limited to that specific month. So I'm trying to learn how to use the Google API functions "Select" and "Where" to specify the range of dates that the query must return.

So if I have the following dates:-

06/29/2019

06/29/2019

06/30/2019

07/01/2019

07/01/2019

07/02/2019

THEN the query must return ALL MATCHES for June where June is requested OR ALL MATCHES for July where July is requested (and the same for all months in a year).

Can anyone help with the syntax I need to improve this =QUERY(Data!$A$2:$F$20,"select A",-1) ?

[Ray Thomas]

Try =QUERY(A:A, "select * where Month(A) = 5")

Where A is the column of dates. In lots of instances months and days start from 0, not 1. So 5 would return those dates in June, 6 would return the dates in July.

Sheet, column A

06/29/2019

06/29/2019

06/30/2019

07/01/2019

07/01/2019

07/02/2019

Output:

06/29/2019

06/29/2019

06/30/2019

[DEBCal]

Thanks Ray. Yes, I did manage to get a working solution like that. However what I didn't mention in my first post is that I'd like to have it more automated by making the "= 5" actually be a cell reference, so I can enter the numerical value for the month that I need (as opposed to recreating the query formula every month).

[Ray Thomas]

OK, suppose your column A contains the dates and cell B1 contains the number of the month you want, then you can use 

=QUERY(A:A, "select * where Month(A)="&B1&"")

It looks a bit strange but that's the way the cell values are referenced in the queries.

As a side note, if you use a query like this to use text in a cell to do some sort of comparison and that text contains a space then you need to add single quotes around "&B1&" so it becomes '"&B1&"'

[Ray Thomas]

A little extra, suppose your cell B1 really does contain '=5 or < 7 or something of that sort, then you can use

=QUERY(A:A, "select * where Month(A) "&B1&"")

I made a small Sheet of the examples I used at https://docs.google.com/spreadsheets/d/1NTGuA7DA5-GVGbvT-aEYMIYmEl358NUjxExDFDVDq-8/edit#gid=0

C1, D1, E1 contains what to reference and C3, D3 and E3 contain the queries.

[DEBCal .]

Fantastic!! Thank you so much. I didn't read anywhere about the ampersand symbol, which would explain why I couldn't get it to work.

--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-visualizati...@googlegroups.com.
To post to this group, send email to google-visua...@googlegroups.com.
Visit this group at https://groups.google.com/group/google-visualization-api.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-visualization-api/45594bdb-8a10-45d8-bebe-7014433d8090%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.