HomeGoogle DocsSpreadsheetHow to Use Date Criteria in Query Function in Google Sheets

How to Use Date Criteria in Query Function in Google Sheets [Date in Where Clause]

Published on

Using dates as the criteria are a complicated part of any criteria formation in the Google Sheet functions. In most cases, you can not use it in its original format. The date criteria in Google Sheets Query function are no exception to this.

It should be converted to text using different approaches, and the usage may vary from one function to another.

I will explain how to use date criteria in the Query function in Google Sheets below.

By saying date criteria, I mean using dates in the Query ‘where’ clause.

Things to Know Before We Talk About Date Criteria [Date in ‘Where’ Clause] in Query Function

When we want to filter a column using a date as a condition (criterion), we must treat it as a string literal in Google Sheets Query.

So the format of using a text in the Query ‘Where’ clause applies to date also.

Text Criterion Hardcoded (directly entered inside the formula):

"select A,B where F='Sold' "

Text Criterion as a Cell Reference:

"select A,B where F='"&E2&"' "

When we come to date, we must additionally use a keyword before the criterion and format it to text. We will learn that below.

Steps: How to Use Date Criteria in Query Function in Google Sheets

When we use a date condition in the above second formula, the date in cell E2 must be either in text string format or we should convert that within the ‘Where’ clause as a text string.

Let’s see how the above two formulas change when the condition is not a text string but a date.

1. When inside formula (hardcoded).

The keyword ‘date’ is used as an identifier to tell the Query that the text is a date.

"select A,B where F=date '2010-08-30' "

2. When cell reference (cell contains a date).

"select A,B where F=date '"&TEXT(E2,"yyyy-mm-dd")&"' "

3. When cell reference (cell contains a text formatted date).

In the below case, the date in E2 must already be in text string format.

"select A, B where F = date '"&E2&"' "

Note: You should follow “yyyy-mm-dd” format in Query formula.

There are two methods to convert the date to a text string in Google Sheets to use in Functions. One is compact and the other is the long-winded approach.

I’ve used the compact method of date-to-text conversion in the formula under point no. 2 above.

You can convert the date to a text string within a cell and use that cell reference in the formula. The above point no. 3 is an example.

Example to Date Criteria in Query Function [Use of Date in Query Function ‘Where’ Clause]

To know how to use date criteria in the Query function in Google Sheets, follow the below examples.

Sample Data (Must be entered in cell ranges A1:F16).

As a side note, if you want to know how I created the above interactive table, please follow this tutorial.

Now see some of the formulas below where the date is the criteria.

One more thing. The name sourcemaster in the below formula is the named range of data. You can instead use sheet reference directly as Sheet1!A1:F16 in the Query.

Query Formula 1

In this Query formula, I’ve used a date condition directly in the formula as a text.

=query(sourcemaster,"select A,B,C,D,E,F where F = date '2010-08-30' ")

Query Formula 2

Here I used the date criteria in the Query function to select rows that fall between two given dates in a column. Here also the date directly used.

=query(sourcemaster,"select A,B,C,D,E,F where F > date '1990-1-1' and F < date '2000-12-13' ")

Query Formula 3

Here the criteria in cell H2 is a cell reference, and that cell contains a date to use as the criterion.

=query(sourcemaster,"select A,B,C,D,E,F where F = date '"&TEXT(H2,"yyyy-mm-dd")&"'")

The alternative option is to convert the date in cell H2 as a text string in another cell, for example, in cell H3. Then use that cell reference as below.

=query(sourcemaster,"select A,B,C,D,E,F where F = date '"&H3&"'")

You can follow the long-winded or compact method of date conversion. I’ve used the following formula in cell H3.

H3 Formula: =text(H2,"yyyy-mm-dd")

Date Criteria in Query Function in Google Sheets - Formula Results

You can find all the above three formula results in the screenshot below in their order from top to bottom.

I hope that you could be able to understand the usage. Any doubt, please drop in comments.

Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

102 COMMENTS

  1. Hi, Prashanth,

    So, I’m trying to QUERY data where a column’s time is less than a certain time specified in a cell.

    I get an “N/A” that says, “Query completed with an empty output.”

    I can’t seem to figure this out, no matter how many videos I watch on the subject.

    Here’s the URL of my Sheet: —removed by admin—

    Formula is in cell A2 in the Early tab.

    Thanks for your help!

    • Hi, Sameer Kumar,

      Here are two examples to help you learn the use of today in Query.

      Popular One:

      =query(A1:B,"select * where A = date '"&TEXT(today(),"yyyy-mm-dd")&"'")

      Suggested:

      =query(A1:B,"select * where A = todate(now())")

  2. Hello,

    I’m trying to add numbers in a column using a one week date period as the selection criteria.

    It would select about 10 rows out of 30+ rows that have a date match, then add the numbers in column K from all the selected rows and display the sum in a single cell.

    Thoughts?

    • Hi, Khalfani,

      That seems possible.

      To sum column K when column A contains dates.

      =Query(A1:K,"Select sum(K) where A >= date '"&TEXT(L1,"yyyy-mm-dd")&"' and
      A <= date '"&TEXT(M1,"yyyy-mm-dd")&"' label sum(K)''")

      Enter the start date in L1 and the end date in M1.

      You can enter this formula in L2.

  3. Hello, I’m looking to show a range between two dates and getting stuck.

    Currently, this works great with dates as static text. But can’t take dates from a set of cells.

    This works:

    =query(ServicesPricings,"select O,P where B > date '2022-5-23' and B = date '"&TEXT(C12,"yyyy-mm-dd")&"' and B <= date '"&TEXT(D12,"yyyy-mm-dd")&"')")

    Can you help me with my date formatting to get through this?

  4. Hello, Could you help:

    On “Daily Report,” in cell A4, what did I do wrong to return empty output?

    — URL (sample sheet) removed by admin —

    • Hi, Sinchai,

      You are applying the date criteria in a text column.

      Column 25 seems formatted to text in your source data. You have a date column, i.e., column 4.

      So replace WHERE Col25 = date '"&TEXT(D1,"yyyy-mm-dd")&"' with WHERE Col4 = date '"&TEXT(D1,"yyyy-mm-dd")&"'.

  5. Thanks for the awesome article! I have learned so much from you.

    I did want to check to see if this was possible or if you knew of a way I could do this.

    I want to pull revenue and conversion rate from separate google sheets by the date and have the sum of revenue and the average conversion rate onto the master spreadsheet, filtered by the date. Is that possible?

    Please let me know if you need any more information.

    Thank you!!

    • Hi, Tim Brill,

      I can’t comment on it until I see your data.

      Can you share a sample sheet below via ‘Reply’?

      Include the data to import in sheet1 and sheet2 and use sheet3 as the master sheet.

      N.B.: I won’t publish the URL.

  6. Hi, I have a similar sheet that is being updated regularly, and I want only to pull the last update for each customer.

    Order Number | Name | Item | QTY | Updated

    1001 | Bill | ABC | 10 | 30/07/2021
    1001 | Bill | DEF | 5 | 30/07/2021
    1001 | Bill | GHI | 5 | 30/07/2021

    1001 | Bill | ABC | 10 | 07/08/2021
    1001 | Bill | DEF | 5 | 07/08/2021
    1001 | Bill | GHI | 7 | 07/08/2021

    1001 | Bill | ABC | 2 | 09/08/2021
    1001 | Bill | DEF | 4 | 09/08/2021
    1001 | Bill | GHI | 2 | 09/08/2021

    I want to pull a query back with this group by order number for the last update and sum the QTY.

    1001 | Bill | 8 | 09/08/2021

    =query(Orders!A1:E,"Select A, B, Sum(D), E group by E Where E = date '"&Text(Max(Orders!E:E),"YYY-MM-DD")&"'",1)

    I am getting an error. Any idea? Thanks

    • Hi, Markdg,

      Please follow the correct Query clause order.

      The “Group by” clause should come only after the “Where” clause. Also, there are other issues.

      Please try the below formula.

      =ArrayFormula(query(A1:E,"select A,B,Sum(D),max(E) where E=date '"&TEXT(max(E3:E),"yyyy-mm-dd")&"' group by A,B"))

  7. Hi,

    I would like to make a Query that I could choose from the drop-down list, and it gives me the input from my analytics tab.

    But I am having trouble doing so with the date function within query or issues with the year data validation cell.

  8. Hi there, I am struggling with an import query that is throwing a formula parse error.

    The objective is to count the number of entries that are not null in Column M (col13) based on the date ranges in column E (col5).

    Thank you in advance! Here is the formula:

    =QUERY(IMPORTRANGE("URL", "Gifts!E2:M"),
    "SELECT COUNT(col13)
    WHERE Col5 >= date '2019-01-01'
    AND Col5 < date '2019-02-01'
    AND Col13 IS NOT NULL
    ")

      • Hey Prashanth,

        Thanks for getting back to me! I went ahead and edited the columns. (of course, I always get mixed up with the numbering and recall systems, thank you for that light bulb.) However, I am still getting a parse error. I checked the import range, and it appears that all is correct there. Any thoughts?

  9. Hi Prashanth. I need help with a query. You seem brilliant at it.

    I have a Sheet that has six fields.

    The two most important fields are Cell D and F with Dates, in these fields.

    I want to query all the data, but where Cell D and Cell F are BOTH less than the date 08/31/2021.

    Here is what I have so Far:

    =query(Sheet1!$A$1:$F$424,"select A, B, C, D, E, F where D < date '"&TEXT(DATEVALUE("08/30/2021"),"yyyy-mm-dd")&"'",1)

    I can seem to get the rest, so that its looking if BOTH cells are less than date 08/31/2021. Thanks for your help in advance.

    • You can try logical AND in Query.

      E.g.:-

      =query(Sheet1!$A$1:$F$424,"select A, B, C, D, E, F where D < date '2021-8-31' and F < date '2021-8-31'")

      An outer Query can remove blanks in both date columns.

      =query(query(Sheet1!$A$1:$F$424,"select A, B, C, D, E, F where D < date '2021-8-31' and F < date '2021-8-31'"),"Select * where Col4 is not null and Col6 is not null")

  10. Hello! Your article is quite useful and informative.

    I am creating a search screen against a number of the columns. For the simple text columns, the query correctly ignores the search fields that are not populated.

    However, when I try to do this with a DATE field, I get the error “Query completed with an empty output.”

    Here’s my query – hopefully, you can provide suggestions on how to tweak this to work:

    =query(Infractions,"select A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P where lower(A) contains '"&lower(A7)&"' and lower(B) contains '"&lower(B7)&"' and lower(D) contains '"&lower(D7)&"' and G contains date '"&TEXT(G7,"yyyy-mm-dd")&"' and lower(H) contains '"&lower(H7)&"'")

LEAVE A REPLY

Please enter your comment!
Please enter your name here