CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Jerome
Jerome

Reputation: 77

Filter by date in Google sheet

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

Upvotes: 0

Views: 1105

Answers (2)

Osm
Osm

Reputation: 2881

01 - Format date and time correctly, these cells have 15, 20 months X_X

enter image description here

02 - Use this formula with i adjusted the col selection,

=query(IMPORTRANGE("1R13d4k1q3P7KbSGCYHP7a5oWLl4BjWGwjNL8-40AVIM","Sheet1!A1:E"),"
       select * where Col1 is not null "&if(len(K2)," 
       and Col3 <= date '"&text(K2,"yyyy-mm-dd")&"' ",)&if(len(K2)," 
       and Col4 >= date '"&text(K2,"yyyy-mm-dd")&"' ",)&" ",1)

enter image description here Note: the yellow table to check the logic you want, return the rows when start is <= TODAY() And End is >= TODAY(), and the returned result is correct, your desierd output end date is proir to TODAY() see the red cells, and its correct in the source data

enter image description here

Upvotes: 1

Argyll
Argyll

Reputation: 9875

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.

Upvotes: 0

Related Questions