Using dates as criteria in Google Sheets functions can be a tricky task. In most cases, you can’t use dates in their original format. The same goes for using date criteria in the Query function in Google Sheets.
Things to Know Before We Talk About Date Criteria [Date in ‘Where’ Clause] in Query Function
When filtering a column using a date as a condition, it must be treated as a string literal in Google Sheets Query. This means that the format of using text in the Query ‘Where’ clause also applies to dates.
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 it comes to dates, we must use a keyword before the criterion and format it to text. Let’s dive into the steps on how to use date criteria in the Query function in Google Sheets.
Steps: How to Use Date Criteria in Query Function in Google Sheets
When using a date condition, the date must either be in text string format or converted within the ‘Where’ clause as a text string.
- When inside the 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' "
- When using a cell reference (cell contains a date).
"select A,B where F=date '"&TEXT(E2,"yyyy-mm-dd")&"' "
- When using a cell reference (cell contains a text formatted date).
In this case, the date in E2 must already be in text string format.
"select A, B where F = date '"&E2&"' "
Note: The format “yyyy-mm-dd” should be followed in the Query formula.
There are two methods to convert dates to text strings in Google Sheets for use in functions. One is the compact method, and the other is the long-winded approach. In the formula mentioned in point number 2, I’ve used the compact method of date-to-text conversion. Alternatively, you can convert the date to a text string within a cell and use that cell reference in the formula, as shown in point number 3.
Example to Date Criteria in Query Function [Use of Date in Query Function ‘Where’ Clause]
To better understand how to use date criteria in the Query function in Google Sheets, let’s look at the following examples:
Sample Data (Must be entered in cell ranges A1:F16).
Now, let’s see some formulas where the date is the criteria:
One more thing. The name “sourcemaster” in the formula is the named range of data. You can instead use the sheet reference directly, such 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. In this case, the dates are directly used in the formula.
=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. In cell H3, I’ve used the following formula.
H3 Formula: =text(H2,"yyyy-mm-dd")
You can find the results of all three formulas in the screenshot below, from top to bottom.
I hope you now understand how to use date criteria in the Query function in Google Sheets. If you have any questions, feel free to drop them in the comments.
