How to use dates in the QUERY function

• Introduction
• Method

Use the QUERY function to return results before, on or after a certain or relative, date.

Introduction

In Google Sheets you use the query function in a number of very powerful ways, including selecting and aggegating data for use in other functions (such as sparkline).

Occasionally you might want to select rows or data from a sheet based on a relative date (e.g. before today or in the future). This can be useful for show future orders, exams due to being sat in the next few days, or new employees starting in the next few weeks who need to be provisioned and trained.

Method

A typical query function might look like this:

Static Query

QUERY(MY_DATA!1:1000, "select B, D, E, F, G, Y where (Y is null OR Y >= 3)")

This will return six columns from your MY_DATA sheet tab, where the value in column Y is either missing or greater than or equal to 3. This is a static query, as it is fixed until you change it. If you wanted to be more flexible with the values that you select, you may wish to move the 3 value into a cell and just reference that. That’s pretty each using the concatenate operator, like this (assuming the selection criteria value of 3 gets put into cell A1).

Dynamic Query

QUERY(MY_DATA!1:1000, "select B, D, E, F, G, Y where (Y is null OR Y >= "&A1&")")

This builds a query dynamically, e.g. whenever the value of cell A1 changes, the results returned from the query will be updated. We can use this same technique to build dynamic queries based on dates. Normally, the format for dates used as criteria in the where clause of a query is yyyy-mm-dd (e.g. 2017-09-01). Helpfully, we can use the text function to provide this date formatting for us.

There are two variations of these formulas. Both will work fine, but the second variation (with an explicit declaration that we are using a date in our criteria) tends to be more robust. This is relevant when dealing with situations where the sheet you are using might not know that your source data or query data should be handled as a date (e.g. the number type is not set for the cell/range).

Dynamic Dates

QUERY(MY_DATA!1:1000, "select B, D, E, F, G, Y where G >= '"&TEXT(NOW(),"yyyy-mm-dd")&"'")

QUERY(MY_DATA!1:1000, "select B, D, E, F, G, Y where date '"&TEXT(NOW(),"yyyy-mm-dd")&"' <= G")

The formulas above use the now function to generate a dynamic query that returns results where the date value in column G is in the future (relative to midnight on the current date). We supply the format to the text function to ensure query can understand our date. You should also make sure that your source data is correctly interpreted as a ‘date’ by using the Format -> Number -> Date menu command. You can also use this same style to reference a cell containing a date (e.g. A1, rather than NOW()). Or you can perform a calculation using the current date:

Date Ranges

QUERY(MY_DATA!1:1000, "select B, D, E, F, G, Y where (G >= '"&TEXT(NOW()-14,"yyyy-mm-dd")&"' and G <= '"&TEXT(NOW()+14,"yyyy-mm-dd")&"')")

QUERY(MY_DATA!1:1000, "select B, D, E, F, G, Y where (G >= date '"&TEXT(NOW()-14,"yyyy-mm-dd")&"' <= G and date '"&TEXT(NOW()+14,"yyyy-mm-dd")&"' >= G)")

This example will return all rows where the date in column G is on or after 14 days ago and on or before 14 days in the future, e.g. within four weeks centred on today. This can be very useful for selecting ‘current’ or relevant rows of data from a larger set.