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.

54 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

      1. great help
        i did:
        =iferror(QUERY(order!A:AT;”select AK where AI= ‘”&B8&”‘ and Y >= date ‘”&TEXT(DATEVALUE(B14);”yyyy-mm-dd”)&”‘ and Y <= date '"&TEXT(DATEVALUE(B18);"yyyy-mm-dd")&"'";0);"0")
        Bring the dinamic values between dates and a criterion
        * where B8 is Data Validation
        *and B14 and B18 are formated as date cell
        * if value is empty in colum AK the result is 0
        works fine
        thanks

  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

    1. Hey Cédric,

      Great question! We can use certain scalar functions inside of our query string to achieve this. Since there were no launches on 1 January, I’ve used 1 July instead and this formula to get back all data on the 1st July:

      =query(Data!$A$1:$H$136,"select C, B where month(B) + 1 = 7 and day(B) = 1",1)

      Note, that if we wanted to get back data in the whole month of January, regardless of year, i.e. all shuttle flights that took off in January, then we could use this formula:

      =query(Data!$A$1:$H$136,"select C, B where month(B) + 1 = 1",1)

      I’ve added both these examples to the end of the linked template Sheet.

      You’ll also notice that I had to add 1 to the month(), that’s because it’s 0-based.

      You can find more about these scalar functions here: https://developers.google.com/chart/interactive/docs/querylanguage#scalar_functions

      Cheers!
      Ben

      1. Thank you Ben!
        I used where Q > date ‘”&Text(A1-1;”yyyy-mm-dd”)&”‘ and Q < date '"&Text(A1+1;"yyyy-mm-dd")&"', but now I see that there is a more direct way of doing it. However, I would have to figure out how to reference a cell inside these scalar functions…

  5. Hello,

    I am trying to filter dates from last week and 3 week out from my spreadsheet. I keep putting the date function and still get an error. I was able to transfer by selecting A,B, C etc but now I just want those 4 weeks to show and when I put some new dates in the master spreadsheet I want it to automatically populate the query function spreadsheet. Can you help me with that?

  6. Hi Ben,

    Please help me to get the totals by dates only from Date and time column in a query function.

    See the sample below:
    12/19/2017 11:15:58 23
    12/19/2017 11:17:58 45
    12/19/2017 12:10:58 56
    12/20/2017 12:17:40 23
    12/20/2017 12:17:40 67
    12/20/2017 12:17:58 89

    Result should be:
    12/19/2017 124
    12/20/2017 179

    Thank you in advance.
    Bijay

  7. Thanks a lot, was very helpfull 🙂
    For I don’t know what reason, i had to change , to ; and columns letters to numbers so here is how the formula looks in my case :
    ={query({OOO!A2:AA1000;’Mauvais Contact’!A2:AA1000;’MER avec Contact’!A2:AA1000;’MER sans contact’!A2:AA1000};”select Col1, Col13, Col5, Col6, Col8, Col10, Col11, Col14, Col15 where Col13 > date ‘”&text(AM2;”yyyy-mm-dd”)&”‘ and Col13 date ‘”&text(AM2;”yyyy-mm-dd”)&”‘ and Col16 date ‘”&text(AM2;”yyyy-mm-dd”)&”‘ and Col19 date ‘”&text(AM2;”yyyy-mm-dd”)&”‘ and Col22 date ‘”&text(AM2;”yyyy-mm-dd”)&”‘ and Col25 <= date '"&text(AM1;"yyyy-mm-dd")&"'";1)}

  8. I am having difficulty in filtering date data Named Range ‘REPORTS’, for example cell B1=Text(today(), "mm-dd-yyyy"), and if I want to count occurrences on this date with query like:
    =QUERY(REPORTS,"select COUNT(C) where B =date'"&text($B$1,"dd-mm-yyyy")&"' AND B='"&$A42&"'")
    But it works fine with > operator!
    any thoughts

    1. Hi Jas,

      Is there a time component to the date in column B? That would be a reason why it doesn’t equal.

      Maybe you could try two conditions, like this pseudo-code:

      B1 >= today and B1 < = today

      as a workaround? That should give you today only then...

      Ben

  9. Hi Ben,

    This is a great article, I have not used Query much before, let alone with dates, so this did help. This has got me half way to what I am trying to do and I was wondering if you could point me in the right direction?
    I have a sheet listing items I need by date and use. Column A is the date (list the full year), then column F is the amount I need. I would like to look between a date range and just return the MAX number in F of that range only.
    Could you give me any pointers?

    Thank you
    Alex

    1. Hey Alex,

      Have you tried something like this (using made up dates):

      =query(data, "select max(F) where A < date '2018-02-01' and A >= date '2017-01-01' ", 1)

      This should calculate max F inside of your date range.

      Cheers,
      Ben

  10. Ran into this issue today and spent 30 mins trying to figure out why I wasn’t getting any results out. Glad I found this article, the solution is really well explained.

  11. Hi Ben,

    Long time reader. Love your site.

    I ran into ONE issue with your “filter between two dates” formula. I am not sure if it was intentional.

    I had a sheet where I was taking data that had multiple rows and values for the same date. You currently have it so that it will only sum the last occurrence for value of the date range. So:

    Date Value
    2018-04-01 1
    2018-04-01 1
    2018-04-02 1
    2018-04-02 1
    2018-04-03 1

    With your formula, it would equal 4 instead of 5 because it would only count starting from the second 2018-04-01.

    So the formula should read Where B >= date…. see below instead of just Where B > date…

    =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)

    You have helped me so much, I thought I would return the favor by pointing something out. If this is intentional I would love to know why in case there is some huge flaw in this method. Thank you again for your help.

    1. Hi Firdaush,

      Thanks for your comment.

      The “>” implies greater than, so it will count everything that is after that date. The “>=” is a greater than or equals to, so will include everything on that date as well.

      Hope that helps!

      Ben

  12. Hi Ben,

    I’m new to Google sheets. Thanks to Your website, it is very useful in my learning. I see there are lots of possibilities comparing dates with QUERY function. But, I’m trying to achieve this with the 2 sheets.

    ResultSheet
    I’d like to display the active employees in a for any given month.

    MasterSheet
    I have employees’ data in 3 columns, EmpName, DateOfJoin, DateOfReleive

    Is this possible with QUERY function? Could you help me with this?

    Thanks.

  13. Artigo Excelente. Muito Obrigado por compartilhar seu conhecimento conosco!
    (Excellent Article. Thank you for sharing your knowledge with us!)

  14. Hi There,

    I Am Andres Felipe,
    im working in a google sheet data base that i get from some google forms, im trying to filter the results of my query function by the timestamp of the responses but its not working.

    =”Select C, X, O, Q, S, U, V Where ( D = ‘Acercamiento’ ) AND ( X >= date ‘ “&TEXT(DATEVALUE(A1),”yyyy-mm-dd”)&” ‘) Order by X desc format X ‘yyyy-mm-dd'”

    this is the query formula i have so far.

    thanks.

  15. Great Article. I am in need of some help with a workflow that I am creating where that references “today’s date” across multiple sheets and multiple columns where today’s date could be present. See example in the provided link.

    https://drive.google.com/open?id=1weYkYevLrDP2xbxykWsHnlRmTtDXbKE5

    There are possibilities for today’s date to be in columns K, M, N, O, or Q, which is consistent across four other sheets in this doc. Ultimately what I am trying to create is a daily task filter (which will be its own sheet) where my employee can follow up on sales leads that are in various stages of the sales process according to date.

    Your help would be greatly appreciated. Thanks

  16. Ben, great article. Is there a way to query multiple columns for the same date range? My sheet is hundreds of columns, and 5 columns are dates. I want to return rows where any of those 5 columns has a date within the desired range. Not all date columns have to fall within the range, just at least one. On top of that, it would be nice if it only returned an item once, even if multiple columns meet the date range.

  17. Could you please advise me how to use query datetime from Google Forms something like this

    =query(‘Sheet1’!$A$1:$B$100,”select A,B where A >= date ‘” & text(A1,”yyyy-mm-dd”) & “‘ and A < date '" & text(B1,"yyyy-mm-dd") & "'",1)

    with A1 in Sheet2? This formula works with 1/1/2018 but not with 1/1/2018, 20:00:00. Thank you.

  18. Interestingly enough, everything described above worked for me only once I removed the ‘date’ and left only something like:
    A < '" & text(B1,"yyyy-mm-dd")

  19. Ben, this is great stuff. I really appreciate it. What I’m trying to figure out is how to select based on the month. I want to be able to take a timestamp from a form entry, and then run the numbers from THIS month.

    I was thinking that I could use something like, MONTH(TEXT(TODAY(),”yyyy-mm-dd”)), which would give me this month, and then search the log for months that match. But I’m no SQL expert here, and I’m not sure how to actually DO that.

  20. Hi i was trying to filter out results if date column in worksheet1 lies within the range of date columns “from date” and “to date” in worksheet2 but getting error.

    Below is my query:

    =query(IMPORTRANGE(“14WxbiYeiY7fX6-V0f2MM4SraphCoa6EHD03F_6NmZ3g”,”Customer_Feedback_Report!A2:L250″),”select Col9 where Col8 = ‘”&Sheet1!A2&”‘ and Col4 > date ‘”&TEXT(‘”&Sheet1!J2&”‘,”yyyy-mm-dd”)&”‘ and Col4 <= date '"&TEXT('"&Sheet1!K2&"',"yyyy-mm-dd")&"' ",1)

    it works if there is no date filter but with date filter it gives an error.

    Col4 , j2 and k2 have datetype mm/dd/yyyy

  21. Okay Ben –

    Always hate to be the last question on a older thread, but here goes:

    I am tracking direct reporting employees’ leave slips in google sheets so I always have the report view available via mobile device (and the intake forms as well)

    Evaluating the data range for (in this instance) current day using your “today()” function inclusion for Query. Start Date and End Date are columns C & D, I am returning columns B & F (Name and Leave Type) for the report.

    Using =QUERY(LeaveSlipFormIntake,”Select B, F WHERE date ‘”&TEXT(TODAY(),”yyyy-mm-dd”)&”‘ >= C AND date ‘”&TEXT(TODAY(),”yyyy-mm-dd”)&”‘ <= D", 1)

    I am returning the correct fields but the wrong records (the evaluation is obviously occurring incorrectly). Is some of your prior examples you were performing scalar functions to manipulate the date against single cell references, can this be done against the column and if so how?

    Thanks

  22. Hi,

    I needed help on the following:

    I wanted to filter by a date range – specifically any date that falls within the last year.

    A B C
    25 01/05/2018
    25 01/07/2018
    25 01/01/2016

    C should say 50 as A1 and A2 fall within the last year.

    This is the best idea i can think of at the moment:
    =SUMIF(A:A,B:B,”>=today()-365)

    But this didn’t work. Most of the above help is about extracting via query. I will be usinig query to get all the information i need to a second sheet and then wanted to filter by date.

    Thanks is advance!

  23. Within a select statement I need to check the cell is an actual date.
    So adding to a working counta… select… something like AND T = isDate()
    Column T contains UTC date time and double numbers in a format of text as they are from an import

    But this alway seems to return 0

    Do you have an example of how to include an isDate test?

Leave a Reply

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