query and importrange function with condition btween two date and sort in order

Question

I want to set query and importrange formula where i want the new sheet to show date that fall into today and yesterday date only and sort in ascending date and time

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1xk0Cvafixr5dayJkBkrzlYLFoA4E_PHYfPNQis12rc8/edit#gid=1702961582","CONSOLIDATE!A1:H"),"Select Col1,Col2,Col3,Col4,Col5,Col6,Col8 where Col1 <= date '"&text(B1,"yyyy-mm-dd")"' and >= date '"&text(B2,"yyyy-mm-dd")"' and order by Col1,Col2")")"

Hi! If the answer below has solved your problem, please remember to Click the Tick, and accept it: This helps anyone else with the same issue, arriving here from a Search Page, to what they need to do! :¬) — Chronocidal
– Chronocidal, Commented Mar 12, 2020 at 9:05

Tom Sharpe · Accepted Answer · 2019-11-14 08:53:17Z

0

A couple of syntax errors, should be:

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1xk0Cvafixr5dayJkBkrzlYLFoA4E_PHYfPNQis12rc8/edit#gid=1702961582","CONSOLIDATE!A1:H"),"Select Col1,Col2,Col3,Col4,Col5,Col6,Col8 where Col1 <= date '"&text(B1,"yyyy-mm-dd")&"' and Col1>= date '"&text(B2,"yyyy-mm-dd")&"' order by Col1,Col2")

i.e. remember to put '&' after the date as well as before, but don't put 'and' before order by.

edited Nov 14, 2019 at 8:53

answered Nov 14, 2019 at 8:32

Tom Sharpe

35.1k5 gold badges29 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

query and importrange function with condition btween two date and sort in order

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related