Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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?

share|improve this question
add comment

4 Answers

up vote 2 down vote accepted

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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!

share|improve this answer
add comment

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())

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.