Do you want to unleash the true potential of date values in Google Sheets Query? Look no further! In this article, we will delve into the fascinating world of date serial numbers and show you how to leverage them to your advantage. So, get ready to master date values in Google Sheets Query!
Introduction
To manipulate a date column using Google Sheets Query, you can follow this formula:
=query({C1:C},"Select Col1 where Col1=date '"&TEXT(E1,"yyyy-mm-dd")&"'",1)This formula filters all the dates in the column C1:C that match the date in cell E1. Pretty neat, right? But here’s the catch – the dates need to be formatted as date serial numbers. Don’t worry, we’ll show you how to do that too!
Now, let’s take a moment to format the dates in column C2:C to date serial numbers.
- Select the range C2:C.
- Go to the Format menu.
- Click on Number and select Number.
By formatting the dates as numbers, you’ll notice that the Query formula in cell G1 only returns the header, with no other values.
image #1
Exciting, right? But we’re just scratching the surface. Let’s explore further!
1. Imported Data Contains Date Values and Its Use in Query
Suppose your imported data has a date serial number column. How can you use the Query formula in such a scenario? Here are two options for you:
Option #1: Number Literal Instead of Date Literal in the Criteria Part
Assuming the data in column C2:C is already converted to date serial numbers, you can use the following Query formula in cell G1 to filter the date values using the condition in cell E1:
=ArrayFormula(query({C1:C},"Select Col1 where Col1="&E1,1))
image #3
In the above formula, we have used a number literal (Col1="&E1) instead of a date literal (Col1=date '"&TEXT(E1,"yyyy-mm-dd")&"') in the criteria part. The output of the formula will also be in number format. To format it to dates, simply include to_date:
=ArrayFormula(TO_DATE(query({C1:C},"Select Col1 where Col1="&E1,1)))Option #2: Using the Format Menu
If you want to use the Query formula with a date literal, you need to format the imported data in column C2:C back to dates from the Format menu. Once formatted, you can use the Query formula as usual:
=query({C1:C},"Select Col1 where Col1=date '"&TEXT(E1,"yyyy-mm-dd")&"'",1)2. Data Contains Date Values Due to a Formula (Expression as Query Data)
Let’s explore another example of how to use date values (date serial numbers) in Google Sheets Query. Suppose you have a DateTime column and want to extract the dates using the INT function. Here’s how you can do it:
=ArrayFormula(query(int(C1:C),"Select Col1 where Col1="&E1,1))You can also include to_date if you prefer:
=ArrayFormula(TO_DATE(query(int(C1:C),"Select Col1 where Col1="&E1,1)))
image #4
The error value you see in the first row of the result is due to the INT function trying to convert the header row in the data, which is a string, into a date. To remove this error, you can use the Label clause:
=ArrayFormula(TO_DATE(query(int(C1:C),"Select Col1 where Col1="&E1&" label Col1'date'",1)))If you prefer using a date literal, you can wrap the INT function instead of the Query:
=ArrayFormula(query(to_date(int(C1:C)),"Select Col1 where Col1=date '"&TEXT(E1,"yyyy-mm-dd")&"' label Col1'date'",1))And there you have it! You’ve just mastered the art of using date values (date serial numbers) in Google Sheets Query.
Thanks for joining us on this date value journey. Feel free to explore further and enjoy the amazing possibilities that Google Sheets Query has to offer!
