CODEWITHSUNDEEP
datefiltergoogle-sheetsgoogle-sheets-formulagoogle-sheets-query
rinatoptimus
rinatoptimus

Reputation: 421

How to filter by date in Google Sheets?

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.

My table with formula:

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?

Upvotes: 3

Views: 34364

Answers (2)

player0
player0

Reputation: 1

you can filter also with FILTER like:

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

0

Upvotes: 6

ADW
ADW

Reputation: 4247

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.

Upvotes: 5

Related Questions