Blog, Google SpreadSheet
How to Use Date Criteria in Query Function in Google Sheets [Date in Where Clause]
Using date as criteria are the complicated part of any criteria formation in Google Sheet functions. Most of the case you can not use the date as it’s. It should be converted to text using different approaches and the usage may vary function to function. I will explain to you how to use date criteria in Query function in Google Sheets below. By saying date criteria, I mean the use of Date in the Query ‘where’ clause.
Note: We have a good number of Query tutorials already here to inspire you
Things to Know Before We Talk About Date Criteria [Date in ‘Where’ Clause] in Query Function
The date is considered as a text string in Query Function in Google Sheets. So how you use text in the ‘where’ clause, that is applicable to date also. See the below example.
When inside formula.
"select A,B where F='Sold'"
When you want to refer a cell value.
"select A,B where F='"&E2&"'"
Steps: How to Use Date Criteria in Query Function in Google Sheets
When we use Date, in the above second point, the date in Cell E2 should be either in text string format or we should convert that within ‘where’ clause as a text string.
1. When inside formula.
The word ‘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 you want to refer a cell value contains a date.
"select A,B where F=date '"&TEXT(E2,"yyyy-mm-dd")&"'"
Here, in the below case, the date in E2 is already converted to text string format, it may be either by using the long-winded approach or compact form of date conversion. See that technique of date conversion below.
"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 form of date to text conversion in the formula mentioned in point no. 2 above. You can convert the date to a text string and use the 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 Query function in Google Sheets, follow the below tutorial.
Sample Data. Should be entered in Cell ranges A1: F16.
As a side note: Are you curious to know how I created the above interactive table? Then follow this tutorial.
You got the sample data above. Now see some of the formulas below where date as criteria. Also “sourcemaster” in the below formula is the named range of data. You can instead use sheet reference directly like “Sheet1!A1: F16”
Query Formula 1
In this Query formula, I’ve used the date directly like 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 Query function to select date difference. 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
=query(sourcemaster,"select A,B,C,D,E,F where F = date '"&TEXT(H2,"yyyy-mm-dd")&"'")
ere the criteria H2 is a cell reference and in that cell, there is a date we put to use as criteria. As already told we can not include date directly in Query. So in the above formula the date we converted to text.
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.
select A,B,C,D,E,F where F = date'"&H3&"'")
You can follow the long-winded or compact method of date conversion for this which is already mentioned at the beginning of this tutorial.
All the above three formula results will be looking like as below, that in the above formula order.
