Filter by date in Google sheet-CodePudding

I am having trouble filtering data by dates which will filter only active permits. I have a set of data coming in through an importrange.(A1)

=ImportRange("https://docs.google.com/spreadsheets/d/1aA_yAOnGa_yJOguCd9f24qWohFj3ciBCwqZiBfIf2Z4/edit?usp=sharing","Formula1!M1:Q10")

Today's date is set (G2)

=TODAY()

I showed the wished result under active permits

Under test formula you can see the formula I put, but only one line comes up.

=query({A1:E},"select Col1,Col2,Col3,Col4,Col5 where Col1 is not null "&if(len(G2)," and Col3 <= date '"&text(G2,"yyyy-mm-dd")&"' ",)&if(len(G2)," and Col4 >= date '"&text(G2,"yyyy-mm-dd")&"' ",)&" ",1)

I tested when instead of getting data from an importrange I get them by copy and paste and the formula works. See Copy of Sheet1 tab

The wished result is to get the query to only import the permits which are active (Today is between start and end date)

Please help

Link to test sheet https://docs.google.com/spreadsheets/d/1JYedYUt15SFJ50P-8Wxv4akYLmZKTkdss9vvvMQ4hUw/edit?usp=sharing

CodePudding user response：

As long as your column C and D are formatted as dates, the following works.

 =filter(A2:E,not(isblank(A2:A),C2:C<=today(),D2:D>=today())

Otherwise, you may need to add to_date() by replacing with D2:D with arrayformula(to_date(D2:D)) (and same with C2:C). Or you may need to parse the dates data with something else. Just be careful of what actually constitutes the dates data in your database. (Date serials? Strings with a format that Google Sheet accepts by default? Or not? etc. In any case, converting particular strings to be compatible for datatime arithmetic in Google Sheet can be its own question.)

Consult official documentation on filter for more info.

CodePudding user response：

I dont have permission to this sheet "https://docs.google.com/spreadsheets/d/1aA_yAOnGa_yJOguCd9f24qWohFj3ciBCwqZiBfIf2Z4/edit?usp=sharing" but this should work

Use this formula

=query({
 ImportRange("1aA_yAOnGa_yJOguCd9f24qWohFj3ciBCwqZiBfIf2Z4","Formula1!M1:Q10")},"
  select * where Col1 is not null "&if(len(G2)," 
  and Col3 <= date '"&text(INDEX(FLATTEN($K$2:$L),1,1),"yyyy-mm-dd")&"' ",)&if(len(G2)," 
  and Col4 >= date '"&text(INDEX(FLATTEN($K$2:$L),COUNTA(FLATTEN($K$2:$L)),1),"yyyy-mm-dd")&"' ",)&" ",1)