3

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?

player0
  • 124,011
  • 12
  • 67
  • 124
rinatoptimus
  • 427
  • 1
  • 5
  • 21
  • 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. – ADW Aug 15 '19 at 05:31
  • I mean fliter data between dates. Now I've edited my query, but it doesn't work. Here's a link: https://docs.google.com/spreadsheets/d/1AT3PT5emwCOoACEzRpMRjGxqihDNp9Y9g_fY9qK8h_I/edit?usp=sharing – rinatoptimus Aug 15 '19 at 05:39

2 Answers2

5

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.

ADW
  • 4,177
  • 1
  • 14
  • 22
5

you can filter also with FILTER like:

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

0

player0
  • 124,011
  • 12
  • 67
  • 124