How to make a query with Google Spreadsheet, that checks for a date

Question

I have a range that goes from B4:H124. All the cells in column B are dates. I would like to retrieve the data from a row (B:H), where the DATE equals to the content of a specific cell, lets say Q4.

How do I construct a proper query?

So far, what I have come up with this:

=QUERY(B2:G124; "select * where B = date '2012-02-28'")

It works, even though the format of the cells in B is DD/MM/YYYY.

How can I change 2012-02-28 to $Q4? When I try, I get the following error:

=QUERY(B2:G124; "select * where B = date '"&Q4&"'")

Invalid query: Invalid date literal [10/02/2012]. Date literals should be of form yyyy-MM-dd.

When I look at the cell, the format is yyyy-mm-dd, but when I doubleclick it, the format changes to DD-MM-YYYY.

What should I do?

Also, how can I sum up the values from the query, without including column B?

Answer 1

I couldn't find the trick for the real date cell to work yet, but you could have a work around if nothing else by adding the the single quote (') before the date in a format: YYYY-MM-DD in the Q4 cell.

That single quote means that the it will be parsed as string.

Answer 2

You can format the date like this:

=QUERY(B2:G124; "select * where B = date '" & text(Q4,"yyyy-MM-dd") & "'")

To sum up the values in column C, do this:

=QUERY(B2:G124; "select sum(C) where B = date '" & text(Q4,"yyyy-MM-dd") & "'")

Not sure how to sum up multiple columns though - still pretty new to this myself!

Answer 3

This should work:

=QUERY(B2:G124; "select * where B = date '"&text(Q4;"yyyy-MM-dd")&"'")

Anyway, I had the same problem and finally it worked for me.

The thing is that you get confused by the message

Invalid query: Invalid date literal [10/02/2012]. Date literals should be of form yyyy-MM-dd.

This is because your Q4 cell's date format seems to be dd/MM/yyyy. I have the same thing: even if this looks like being dd/MM/yyyy you just have to put it like it's given in the second line of my reply herein above.

Answer 4

Put your desired date in a new cell like Q3 in this format: 2/28/2012

Then in the Q4 cell type =YEAR(Q3) & "-" & DEC2OCT(MONTH(Q3), 2) & "-" & DAY(Q3)

This will get the date from the Q3 cell and convert it to a string in the Q4 cell.

This is more helpful if you want the current date. Instead of getting the date from Q3 your can use now() to get today's date like so: =YEAR(now()) & "-" & DEC2OCT(MONTH(now()), 2) & "-" & DAY(now())

Answer 5

The final should be:

=QUERY(B2:G124; "select * where B = date ' "  & text( Q4 ,"yyyy-MM-dd") & "'")

Where Q4 is a normal date formatted cell where you may type in a date.

Copy the above to your notepad and enlarge the font size to what's all about.

It's hard to see the single ' within the double " here.

Answer 6

You can also calculate the date outside the string and drop the date, for example

=QUERY(B2:G124; "select * where B = '" & Q4)

Q4 effectively gets passed as an integer, which is what QUERY transforms the date to behind the scenes anyway.

Answer 7

Using Google's QUERY function:

To sum multiple columns, it's important to include the label parameter at the end of the query

=QUERY(B2:G124; "select sum(C) + sum(D) + sum(E) + sum(F) + sum(G) label sum(C) + sum(D) + sum(E) + sum(F) + sum(G) '' ") 

note: column references are case sensitive & the 2 single quotes at the end of the query are required.

To add a where clause referencing a cells date value: from s6mikes solution

=QUERY(B2:G124; "select * where B = date '" & text(Q4,"yyyy-MM-dd") & "') 

The 2 queries combined...

=QUERY(B2:G124; "select sum(C) + sum(D) + sum(E) + sum(F) + sum(G) where B = date '" & text(Q4,"yyyy-MM-dd") & "' label sum(C) + sum(D) + sum(E) + sum(F) + sum(G) '' ")

Answer 8

You have to change the type of data in your reference cell to text. And it will work smoothly.