Search code examples
google-sheetsgoogle-sheets-query

Google Sheet Querying a Date Value from a cell in another sheet

I would like to ask if anyone knows the exact syntax to get this query working on google sheets.

I currently have 3 Sheets:
1. Sheet A, RAW DATA (where all my Raw Data is)
2. Sheet B, Main Page (where I have my user input on the date)
3. Sheet C, Filtered Data (where I would like to use the Query to pull out filtered data from Sheet A)

So I have set up a generic query goes like this in Sheet C, it works:

=QUERY('RAW DATA'!A:J, "SELECT * WHERE F > date '2018-03-20' AND F < date '2018-03-22' AND F IS NOT NULL ORDER BY F",1)

I would now like to replace the 2 dates, with user input dates, which are B5 and D5 in Sheet B.

I have tried this syntax:

=QUERY('RAW DATA'!A:J, "SELECT * WHERE F > date '"&Main Page!B5&"' AND F < date '"&Main Page!D5&"' AND F IS NOT NULL ORDER BY F",1)

Do note that B5 and D5 have already been set up with data validation rules to ensure that only date values are allowed.

Could anyone point out my mistake?

Thank you!

Solution

  • The date provided to QUERY must be:

    • a string of
    • format yyyy-mm-dd

    Try changing from

    date '"&Main Page!D5&"'

    to

    date '"&TEXT(Main Page!D5,"yyyy-mm-dd")&"'