Smart Chips in Google Sheets allow us to add richer information to our Sheets, beyond the standard data contained in cells. They help us work more seamlessly by bringing information from external sources into our Sheets.
In this post and video, we take a comprehensive look at the smart chip feature. You’ll learn what they are, how to work with them, and see some example workflows to understand how they can enrich your Google Sheets workflows.
In a nutshell, the problem occurs because dates in Google Sheets are actually stored as serial numbers, but the Query function requires a date as a string literal in the format yyyy-mm-dd, otherwise it can’t perform the comparison filter.
This post explores this issue in more detail and shows you how to filter with dates correctly in your Query formulas.
The Problem with Dates in the QUERY Function
Suppose you have a dataset of Space Shuttle launches from the first launch in 1981 through to the final launch in 2011. You want to filter the data to look only at the subset from the year 2000 onwards.
To begin, you might try the following syntax:
=QUERY(Data!$A$1:$H$136,"select C, B where B > '1/1/2000'",1)
Unfortunately, the output of such a query is blank:
If instead we remove the single quotes from around the date and try again, we get a #VALUE! error because the Query formula can’t perform the comparison:
Alas, what are we to do!
Neither of these “standard” formats work, because the dates are not in the correct format for the Query function.