Filtering with dates in the QUERY function

If you’ve ever tried to filter on a date column in the Query function in Google Sheets, then you know how tricky it can be.

In a nutshell, the problem occurs because dates in Google Sheets are actually stored as serial numbers, but the Query function requires a date as a string literal in the format yyyy-mm-dd, otherwise it can’t perform the comparison filter.

This post explores this issue in more detail and shows you how to filter with dates correctly in your Query formulas.

The problem

You might for example try the following syntax:

=QUERY(Data!$A$1:$H$136,"select C, B where B > '1/1/2000'",1)

Unfortunately, the output of such a query is blank:

Incorrect dates in the Query formula

If instead we remove the single quotes from around the date and try again, we get a #VALUE! error because the Query formula can’t perform the comparison:

Incorrect dates in the Query function

Alas, what are we to do!

Neither of these “standard” formats work, because the dates are not in the correct format for the Query function.

Correct syntax for dates in the Query function

Per the Query Language documentation, we need to include the date keyword and ensure that the date is in the format yyyy-mm-dd to use a date as a filter in the WHERE clause of our Query function.

Putting aside the Query function for a moment, let’s consider that "select..." string.

The new syntax we want will look like this:

date_column > date '2000-01-01'

Our challenge is to create a text formula to create this syntax for us, inside our query function.

Dealing with the text function first, starting with our required date of 1/1/2000 and working outwards:

First, we convert it to a serial number format with the DATEVALUE() wrapper:

=DATEVALUE("1/1/2000")

The output of this formula is a number:

36526

Then the TEXT() function converts it to the required format for the Query formula by specifying a format of "yyyy-mm-dd":

=TEXT(DATEVALUE("1/1/2000"),"yyyy-mm-dd")

The output of this formula is a date in the desired format:

2000-01-01

Next we add single quotes around the new date format, with the "'" syntax. Finally, we insert the word date into the query string, to give:

="select C, B where B > date '"&TEXT(DATEVALUE("1/1/2000"),"yyyy-mm-dd")&"'"

which gives or desired output:

select C, B where B > date '2000-01-01'

That’s the syntax challenge done!

We can now plop that string into the middle argument of our Query function as per usual, and it’ll do the trick for us.

In this case, I was using a table of Space Shuttle mission data from Wikipedia, which contains a column of launch dates.

I used the IMPORTHTML() function to import that table into my Google Sheet, into a tab called Data in the range A1:H136. There’s a link to this dataset and worksheet at the end of the post.

This Query formula returns all of the Space Shuttle missions after 1 January 2000:

=QUERY(Data!$A$1:$H$136,"select C, B where B > date '"&TEXT(DATEVALUE("1/1/2000"),"yyyy-mm-dd")&"'",1)

The output of our formula is now returning the correct, filtered data:

Correct syntax to use dates in the query function

Referencing a date in a cell

The formula is actually simpler in this case, as we don’t need the DATEVALUE function. Assuming we have a date in cell A1 that we want to use in our filter, then the formula becomes:

=QUERY(Data!$A$1:$H$136,"select C, B where B > date '"&TEXT(A1,"yyyy-mm-dd")&"'",1)

Example showing filter between two dates

Again, it’s relatively simple to extend our formula by adding a second date clause after the AND keyword:

=QUERY(Data!$A$1:$H$136,"select C, B where B > date '"&TEXT(A1,"yyyy-mm-dd")&"' and B <= date '"&TEXT(B1,"yyyy-mm-dd")&"'",1)

Using today’s date as a filter

Substitute the TODAY() function into our formula:

=QUERY(Data!$A$1:$H$136,"select C, B where B > date '"&TEXT(TODAY(),"yyyy-mm-dd")&"'",1)

Can I see an example worksheet?

Yes, here you go.

10 thoughts on “Filtering with dates in the QUERY function”

  1. Is there an analogous method of summing durations (hh:nn) in queries? For me, sometimes the work when the original data is in hh:nn format. Sometimes I have to change them to a serial number for the query to work.

  2. Dear Ben,
    I have no words to thank you for your wonderful examples.
    I did the spreadsheet with your “query” example and it worked for me. but I have a small problem:
    The cell D1 is a list from an interval, there I choose the sellers ID (numeric), and in the formula I have:
    AND J = '' & D1 &
    and it does not bring me any results, I think because it’s numeric, I tried the following but it did not work:
    AND J = '"& TEXT (D1;" ########### ") &"'
    But it did not work 🙁
    Please, can you help me? Thank you.

    1. Hey Fernando,

      Try it without any additional single quotation marks, like this:

      J = " & D1

      If the datatype in cell D1 (number) matches the datatypes in column J (e.g. numbers as well) then it should work for you.

      Cheers,
      Ben

  3. Dear Ben,
    This year I have been creating a monthly summary of posts and updates associated with all things GSuite. It occurred to me after nine months that I should really be storing all the links in some sort of database. My question is how to automate the process of turning that into a monthly post (https://readwriterespond.com/2017/10/scripting-automated-solution/).
    I have been getting on the GAS and am thinking that QUERY might be a part of my solution. I am therefore trying to get everything working in Sheets first. I have followed your guide to QUERY (https://www.benlcollins.com/spreadsheets/google-sheets-query-sql/), even adding in two dynamic selectors (is that what they are called?) that I got from your work on VLOOKUPS (https://www.benlcollins.com/spreadsheets/vlookup-wildcard/). My question is filtering by dates. I have followed your instructions, but fear that I maybe limited as I have recorded my dates using DD-MM-YYYY. Sheets recognises this as a date as a formatted the cells as ‘DATES’. My spreadsheet settings have also been changed to ‘Australia’. I am wondering if you have any thoughts or suggestions on this? Here is a link to my sheet – https://docs.google.com/spreadsheets/d/17Szdpgp3viqi2TsvYhF2Nfc97fQhWkhXQ-ZK9x9XQRM/edit#gid=1616853743&range=F2

    1. Hey Aaron!

      Cool project!

      I made a copy of your sheet and changed the formula to this:

      =query(gsuite, "Select F, G Where B = '"&A$2&"' and A = '"&B$2&"' and C > date '"&TEXT("2017/10/1","yyyy-mm-dd")&"' and C < = date '"&TEXT("2017/10/30","yyyy-mm-dd")&"'", 1)

      I made two changes: 1) put quotation marks around your start and end dates in the TEXT formulas; and 2) you had B instead of C in your last date comparison.

      I think this query formula now works as you intended.

      Cheers,
      Ben

  4. Hi Ben,
    Thanks! You solved my problem!
    But I got into another one… For some reason, it works fine when I have ” when Q > date ‘”&Text(F17, “yyyy-mm-dd”)&”‘ “, but the query is empty if I change the “>” sign for a “=” sign.
    How can I filter my query so that it matches a specific date? (in your example, it would be for instance a list of shuttle missions on January 1st.
    Thank you in advance.
    Cédric

Leave a Reply

Your email address will not be published. Required fields are marked *