Using dates as criteria in Google Sheets functions can be quite challenging. Unlike other criteria, dates cannot be used in their original format in the Query function. They need to be converted to text using different approaches, and the usage may vary from one function to another. In this article, we will explore how to effectively use date criteria in the Query function in Google Sheets and simplify the process for you.
Understanding Date Criteria in the Query Function
Before we delve into the steps, it’s essential to understand how date criteria work in the Query ‘where’ clause. When filtering a column using a date as a condition, you must treat it as a string literal in Google Sheets Query. This means that the format used for text criteria applies to dates as well.
Here are two examples of text criteria:
- Hardcoded (directly entered inside the formula): “select A,B where F=’Sold'”
- Cell Reference: “select A,B where F='”&E2&”‘”
When it comes to dates, you need to use a keyword and format the criteria as text. Let’s explore the steps to effectively use date criteria in the Query function.
Steps to Use Date Criteria in Query Function in Google Sheets
To use a date condition in the Query function, you need to ensure that the date is either in text string format or converted within the ‘where’ clause as a text string.
-
When using a hardcoded date inside the formula, use the keyword ‘date’ to indicate that the text is a date: “select A,B where F=date ‘2010-08-30′”
-
When using a cell reference that contains a date, format the date as a text string within the ‘where’ clause: “select A,B where F=date ‘”&TEXT(E2,”yyyy-mm-dd”)&”‘”
-
If the cell reference contains a text-formatted date, ensure that the date is already in text string format: “select A, B where F = date ‘”&E2&”‘”
Note: The date in the Query formula should follow the “yyyy-mm-dd” format.
There are two methods to convert dates to text strings in Google Sheets for use in functions. The first method is more compact and involves using the TEXT function within the formula itself. The second method involves converting the date to a text string within a cell and referencing that cell in the formula.
Example of Using Date Criteria in Query Function
To understand the usage of date criteria in the Query function, let’s consider the following examples:
Sample Data:
(Must be entered in cell ranges A1:F16)
Now, let’s look at some formulas that utilize date criteria:
Query Formula 1
In this formula, a date condition is directly used as text within the formula:
=query(sourcemaster,”select A,B,C,D,E,F where F = date ‘2010-08-30′”)
Query Formula 2
Here, the date criteria are used to select rows that fall between two given dates in a column:
=query(sourcemaster,”select A,B,C,D,E,F where F > date ‘1990-1-1’ and F < date ‘2000-12-13′”)
Query Formula 3
This formula utilizes a cell reference with a date as the criterion:
=query(sourcemaster,”select A,B,C,D,E,F where F = date ‘”&TEXT(H2,”yyyy-mm-dd”)&”‘”)
An alternative option is to convert the date in the referenced cell to a text string in another cell and use that cell reference in the formula.
I hope this article has provided you with a clear understanding of how to effectively use date criteria in the Query function in Google Sheets. If you have any doubts, please feel free to drop a comment. For more helpful tips and tricks, visit Crawlan.com.
Note: This article was originally published on crawlan.com.
