How to filter by date in Google Sheets?

Question

I have a basic table with simple data. I need to filter data by date, for example, I want to see only items with dates between 03/03/2016 and 03/04/2016.

This query looks like this:

select A, B WHERE A >= 42432 AND A <= 42463

If I change date in cells B1 and C1, numbers (42432 and 42463) are changing as well. But items are not being filtered.

So what should I do?

Solution 1

Filtering dates with the query() function requires special syntax: date 'yyyy-mm-dd'.

=query(A:B,"select A, B WHERE B >= date '2016-02-03' AND B <= date '2016-04-03'")

If you need to reference a cell with dates in them, the cells will have dates as numbers. You'll need to convert the format to yyyy-mm-dd like so:

=query(A:B,"select A, B WHERE B >= date '"&text(C1,"yyyy-mm-dd")&"' AND B <= date '"&text(D1,"yyyy-mm-dd")&"'") 

Ref docs here.

Solution 2

you can filter also with FILTER like:

=FILTER(A4:B, B4:B >= B1, B4:B <= C1)

Comments

[ADW]

Could you clarify the question? Are you looking to sort by date or filter data between dates? The query string in E4 is trying to query column A for dates but those are in column B. Perhaps there is a mixup in setting up the demo sheet.

[rinatoptimus]

I mean fliter data between dates. Now I've edited my query, but it doesn't work. Here's a link: docs.google.com/spreadsheets/d/…