Google Query Language will not display range located between two dates provided in cells

Question

I have a query pulling from a large data list, and it works until I try to filter the data (Range Q) for two date ranges.

I've tried changing around the date format (yyyy/mm/dd, yyyy-mm-dd, etc), but it still does not seem to want to work. The value for $E$6 and $E$7 are pulling from data validation, and the Range Q is grabbing from a row with dd/mm/yyyy format.

=QUERY('Deal Data'!A1:X999, 
 "select B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X 
  where ("&IF($E$8  = "All", , "D = """&$E$8&"""  and ") &
           IF($E$9  = "All", , "C = """&$E$9&"""  and ") &
           IF($E$10 = "All", , "U = """&$E$10&""" and ") " 
        Q >= date """&TEXT($E$6,"dd/mm/yyyy")&""" 
    and Q <= date """&TEXT($E$7,"dd/mm/yyyy")"""  and " &
        ("F = """&$E$11&""")"))

The formula works when I exclude the data range:

=QUERY('Deal Data'!A1:X999, 
 "select B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X 
  where ("&IF($E$8  = "All", , "D = """&$E$8&"""  and ") &
           IF($E$9  = "All", , "C = """&$E$9&"""  and ") &
           IF($E$10 = "All", , "U = """&$E$10&""" and ") & 
        ("F = """&$E$11&""")"))`

The result I am getting is an #ERROR result.

Any help would be very appreciated!

player0 player0 · Accepted Answer · 2019-02-07T23:33:42

this is how query should look like if you want to query out data between dates

=QUERY(A2:B, "select * where A >= date '"&TEXT(C1, "yyyy-mm-dd")&"' 
                         and A <= date '"&TEXT(C2, "yyyy-mm-dd")&"'", 0)

Google Query Language will not display range located between two dates provided in cells

1 Answers