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

0
339
USAGE OF DATE IN QUERY SYNTAX

Using date as criteria is 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 use may wary function to function. I will explain 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

Date is considered as a text string in Query Function in Google Sheets. So using text values in the ‘where’ clause is applicable to date also.

  1. “select A,B where F=‘Sold’” – when inside formula
  2. “select A,B where F=‘”&E2&”’” – when you want to refer a cell value.

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 text string.

  1. “select A,B where F=date ‘2010-08-30′” – when inside formula. The word ‘date’ is used as an identifier to tell the Query that the text is a date.
  2. “select A,B where F=date ‘”&TEXT(E2,“yyyy-mm-dd”)&“‘” – when you want to refer a cell value contain date.
  3. “select A,B where F = date'”&E2&”‘” – Here in this 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.

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

There are two methods to convert date to text string in Google Sheets to use in Functions. One is compact and the other is long-winded approach. I used the compact form of date to text conversion in the formula mentioned in point no. 2 above. You can convert the date to 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 named range of data. You can instead use sheet reference directly like “Sheet1!A1:F16”

Query Formula 1

In this Query formula I used 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”)&“‘”)

Here 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 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 in the beginning of this tutorial.

All the above three formula results will be looking like as below, that in the above formula order.Date Criteria in Query Function in Google Sheets - Formula Results

Hope you understand. Any doubt please drop in comments.

LEAVE A REPLY

Please enter your comment!
Please enter your name here