Google Sheets – How to Make a Query That Checks for a Date

google sheetsgoogle-sheets-query

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?

Best Answer

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.

Related Topic