Google Sheets QUERY Function Advanced Application Examples 12 (Continued: Handling Dates, Date-Times, and Times)

This is the 12th installment of my note on the QUERY function, the most powerful aggregation function in Google Sheets.

👇 The previous QUERY function series are summarized in this magazine. A must-read for those who want to master the QUERY function at a high (or advanced) level!

Last time, I wrote about how to use the QUERY function in combination with literals for dates, date-times, and times, as well as comparison operators.

This time,

1. Understand the description rules for date, date-time, and time literals

2. Understand that comparison operators can be used with dates, date-times, and times

3. Understand scalar functions that can process dates, date-times, and times 👈 Understanding this time

4. Understand how to use string comparison operators with dates, date-times, and times

scalar functions will be covered.

3. Understand scalar functions that can process dates, date-times, and times

The third point for mastering dates, date-times, and times in the QUERY function is scalar functions.

$$
\begin{array}{lll}
\text{No}&\text{Scalar Function}&\text{Explanation}\\ \hline
\text{1}&\text{year()}&\text{Returns the year from a date or date-time value as a number}\\ \hline
\text{2}&\text{month()}&\text{Returns the month from a date or date-time value as a number *Month values start at 0}\\ \hline
\text{3}&\text{day()}&\text{Returns the day from a date or date-time value as a number}\\ \hline
\text{4}&\text{hour()}&\text{Returns the hour from a date-time or time value as a number}\\ \hline
\text{5}&\text{minute()}&\text{Returns the minute from a date-time or time value as a number}\\ \hline
\text{6}&\text{second()}&\text{Returns the second from a date-time or time value as a number}\\ \hline
\text{7}&\text{millisecond()}&\text{Returns the millisecond from a date-time or time value as a number}\\ \hline
\text{8}&\text{quarter()}&\text{Returns the quarter from a date or date-time value as a number}\\ \hline
\text{9}&\text{dayOfWeek()}&\text{Returns the day of the week from a date or date-time value as a number *Starts at 1 for Sunday}\\ \hline
\text{10}&\text{now()}&\text{Returns the current date and time *GMT time zone}\\ \hline
\text{11}&\text{dateDiff()}&\text{Returns the number of days between two dates or date-times as a number.}\\ \hline
\text{12}&\text{toDate()}&\text{Converts the specified value to a date value (date type). *Can convert date-times or numbers (epoch values) to dates}\\ \hline
\end{array}
$$

https://developers.google.com/chart/interactive/docs/querylanguage?hl=en#scalar_functions

There are 15 scalar functions that can be used in any of the select, where, group by, pivot, order by, label, or format clauses, and of those, upper and lower aside, 12 scalar functions are related to date, date-time, and time types.

Let's learn their behavior and usage through examples in select and where clauses, as well as through exercises.

3-1 to 3-3. Extracting Year, Month, and Day from a Date: year(), month(), day()

In a QUERY function query statement, scalar functions that retrieve the year, month, and day as numbers from date or date-time type data are

These are them.

Since these three are overwhelmingly more well-known and frequently used than other scalar functions, many of you have likely used them before.

How to use the scalar functions year(), month(), and day()

They have the same names and (mostly) the same functionality as sheet functions, so they are easy to understand. (The month is off by one, but I will explain that later.)

With a formula like this, you can filter data based on the condition that the fourth column matches 'A', and then from the date in the first column output the year, month, and day as numerical values.

year(), month(), and day() can be used not only for date-type columns but also

date-time type columns and

time type columns.

While you can use them, you generally wouldn't retrieve the year, month, or day from a time-type column.

The date portion of a non-existent time is treated as the date 1899/12/30 with a serial value of 0. (The month discrepancy will be discussed later.)

[Note] The QUERY function scalar function month() starts at 0 and is off by one month

I wrote that the scalar functions for retrieving the year, month, and day—year(), month(), and day()—can be used just like sheet functions, but you must be careful with month().

Even in the official documentation,

As stated here, month() used in a QUERY function query string starts at 0.

It returns the previous number, with January as 0, February as 1, March as 2, ... and December as 11.

If you have handled dates in GAS before, you might be used to this because it is the same as the getMonth() method specification, but

if you don't do programming, it might feel quite strange.

For now, it's just how it works, so let's get used to it.

So, when you use the scalar function month() to extract and handle the month from date data,

month(Col1)+1

👆 Do it like this.

Since the result of month() is a number, you can use the + operator here as usual.

Let's just memorize: "When using month() in the QUERY function, add +1"!

Q1. I want to specify the year and month in cells and extract data using the QUERY function.

Now, let's move on to the challenge.

Using the data in A1:D8, what formula should you construct if you want to use the QUERY function to extract data where the year and month of the date data in the first column match the year in cell G1 and the month in cell G2?

Please use the sample data below.

日付	日時	時刻	テキスト
2025/07/01	2025/07/01 10:45	10:45	A
2025/07/02	2025/07/02 11:30	11:30	B
2025/12/12	2025/12/12 14:22	14:22	A
2026/01/22	2026/01/22 16:01	16:01	A
2025/07/05	2025/07/05 17:11	17:11	B
2025/07/06	2025/07/06 18:50	18:50	B
2026/02/20	2026/02/20 21:40	21:40	A

Let's think about it!

If you find this too easy, try challenging yourself with an alternative solution that constructs the formula without using scalar functions, but rather using the scope learned up to the previous lesson (literals + comparison operators).

↓↓
The answer starts here.
↓↓

A1. Extract data using the QUERY function by specifying the year and month in cells

Here is the answer.

We extract the year and month from column 1 (Col1) using scalar functions, and then use the = operator to check if they match the year and month values in G1 and G2.

The key point is what I mentioned earlier:

"When using month() in the QUERY function, add 1"

is that right?

month(Col1)+1 = "&G2

By writing it this way, we adjust for the fact that the month() scalar function starts at 0, which causes a one-month offset.

Also, since G1 and G2 are numbers, single quotes are not required.

Was that simple enough?

A1. [Alternative Solution] Extracting data with the QUERY function by specifying the year and month in cells without using scalar functions

This is an alternative approach to creating the formula without using scalar functions, and there are several ways to do it.

For example, this formula above is relatively short. (This is just one example.)

The content is a bit complex, but it is an application of the formula used in the previous topic 7 to extract data within a period.

QUERY Function 11 Q7. I want to extract data within a period between two dates specified in cells

By referencing the year and month in cells G1 and G2 within the formula, it generates a query string like the one below.

where Col1 >= date '2025-7-1' and Col1 < date '2025-8-1'

Since we are retrieving July, we could calculate the last day of the month (the 31st for July), but...

Here, by setting it to be less than the 1st of the following month (excluding the 1st)

Col1 < date '2025-8-1'

we can retrieve only the data for July 2025 simply by shifting the month with G2+1.

However, when the month in cell G2 is 12, we need to add 1 to the year and set the month to 1, so we adjust that part here:

date '"&G1+(G2=12)&"-"&(G2+1)^(G2<12)&"-1'"

It is adjusted in these two places.

This formula utilizes the characteristics that TRUE is treated as 1 and FALSE as 0 when combined with operators, and that
raising a number to the power of 0 returns 1 regardless of the number.

However, it is a bit difficult to understand.

Although the formula becomes longer, the following formula, which converts it to a date using the DATE function and then to a string using the TEXT function, might be easier to read.

Alternatively, you can find the last day of the month and use less than or equal to <=.

Alternative solution 3 utilizes the property that the DATE function returns the last day of the previous month when the day argument in DATE(year, month, day) is set to 0.

I have introduced three alternative solutions, but I hope you have realized that when filtering by year and month in the QUERY function, using the scalar functions year() and month() is overwhelmingly easier than these alternatives.

In fact, extraction based on year and month conditions can be even more concise when using the string comparison operators that will appear later. I will introduce this next week!

For now, the basic method for extraction using year and month as conditions in the QUERY function's where clause is to use scalar functions.

Make sure you understand this thoroughly.

3-4 to 3-7. Extracting hour, minute, second, and millisecond from date-time and time using hour(), minute(), second(), millisecond()

In the QUERY function's query string, the scalar functions to retrieve hours, minutes, seconds, and even milliseconds as numbers from date-time or time type data are

These are them.

How to use the scalar functions hour(), minute(), second(), and millisecond()

Using the same method as before, you can use a formula like 👆 this to filter by the condition that the 4th column matches A, and then return the results of extracting the hour, minute, and second as numbers from the 2nd column (date-time type) respectively.

Date-time types are not the only ones; it is also possible to target time type columns.

However,

it cannot be used on date type columns.

The year(), month(), and day() functions mentioned earlier could be used on time type columns, but these cannot.

By the way, even if you target a column that displays 24 hours or more with the display format set to "Elapsed time" ([h]:mm:ss),

the scalar function hour() cannot return a value of 23 or higher.

24:00 becomes 0, and anything beyond that is treated as extracting the hour, minute, and second from the time portion of the date-time display.

Also, although you might not use it often, there is a scalar function that extracts only milliseconds

as well.

There aren't many use cases for this, so there is no exercise for it.

3-8. Extracting the quarter from dates and date-times using quarter()

In a QUERY function query string, the scalar function to get the quarter as a number from date or date-time type data is

.

How to use the scalar function quarter()

Dividing a year into four parts of three months each,

It returns a numeric value. This can be used for quarterly performance aggregation.

Q2. I want to retrieve first-half data for a specified year

Let's practice with a simple task. If you want to extract data for the first half (January to June) of 2025 from the data in A1:D8 on the left using the QUERY function's quarter() function, what formula should you construct?

The data is the same as in Task 1. Let's think about it!

↓↓
The answer starts here.
↓↓

A2. Retrieve first-half data for a specified year

Here is the answer.

Since the first half (January to June) consists of the 6 months of the first and second quarters,

quarter(Col1) < 3

can simply be added to the year condition using 'and'.

Of course, you can also use month() to get the same result:

This will also yield the same result.

Rather than in the where clause, quarter() is often used in aggregations using group by and pivot clauses which we will cover later.

3-9. Getting the day of the week from a date or date-time: dayOfWeek()

In a QUERY function query string, the scalar function to get the day of the week as a number from date or date-time type data is

.

How to use the scalar function dayOfWeek()

With Sunday as 1,

it returns the day of the week as a number like this. You can use it with the same logic as the sheet function WEEKDAY function.

The official documentation writes it as dayOfWeek() in camelCase, but since you don't need to worry about case sensitivity within the query, dayofweek() or DAYOFWEEK() are also fine.

This is often used in conjunction with the format clause which will appear in a later installment.

Q3. I want to extract only weekend data using the shortest possible formula.

Now, let's try a slightly advanced challenge.

From the data on the left (👇 please use this data)

日付	日時	時刻	テキスト
2025年7月1日(火)	2025/07/01 10:45	10:45	A
2025年7月2日(水)	2025/07/02 11:30	11:30	B
2025年7月3日(木)	2025/07/03 14:22	14:22	A
2025年7月4日(金)	2025/07/04 16:01	16:01	A
2025年7月5日(土)	2025/07/05 17:11	17:11	B
2025年7月6日(日)	2025/07/06 18:50	18:50	B
2025年7月7日(月)	2025/07/07 21:40	21:40	A

, if you want to extract only weekend data from the first column using the QUERY function, what kind of formula should you create?

However, you must create the shortest formula possible.

Let's think about it!

↓↓
The answer starts here.
↓↓

A3. Extract only weekend data using the shortest possible formula

Here is the answer.

How was that? Were you able to make it this short?

Let me explain.

First, I usually recommend using dayofweek(Col1) to specify columns with Col, but since we have the constraint of "using the shortest possible formula" this time, I am using the column letter specification.

Also, the scalar function dayOfWeek() starts on Sunday, so Sunday is 1 and Saturday is 7.

Unfortunately, there is no option like the second argument of the sheet function WEEKDAY function to change the start day from Sunday to Monday.

The numerical values corresponding to the days of the week for the scalar function dayOfWeek() are fixed.

Therefore, if you write the formula normally, it becomes

This results in a formula that describes the condition where dayofweek(A) matches 1 or 7.

However, with this, you end up connecting two similar descriptions with or, which inevitably makes it long.

What you can use here is the hidden arithmetic operator % (modulo), which is not officially documented.

QUERY Function 3 Arithmetic operators (actually % modulo can also be used)

When you find the remainder of the numerical values 1 to 7 (Sunday to Saturday) divided by 7, Sunday remains 1, but

for Saturday, which was 7, since there is no remainder when divided by 7, it becomes 0.

In other words,

and it is possible to rephrase the condition like this,

The remainder of dayOfWeek() divided by 7 is less than 2

can be written in the query as

dayofweek(A)%7 < 2

!

In this way, in the QUERY function, the scalar function dayofweek(), which retrieves the day of the week as a number, is often used in combination with the format clause and the arithmetic operator % (modulo) is.

Be sure to remember this.

3-10. Outputting the current date and time with now() *However, in the GMT time zone

Actually, you can also use the now() function in QUERY function query statements.

The scalar function that returns the current date and time is also just

is.

How to use the scalar function now()

Although a header is attached, it can return the date and time just like the now() function.

Like the sheet function NOW, it is a volatile function that updates when the sheet is updated, or every minute if configured.

Caution is required when using it in a QUERY function that performs heavy processing.

But before that... the current time returned by the scalar function now() is,

It's the current time in the GMT time zone!

As you can see by looking at 👆, it is exactly 9 hours behind the Japan Standard Time (JST) returned by the sheet function NOW().

It seems like it could be useful, but considering the 9-hour time difference, I think it's quite difficult to find a good use case for it in a Japan Standard Time environment.

With sheet functions, you can quickly adjust it to 9 hours later by adding +"9:00", but you probably cannot do this within a query string to convert it to Japan Standard Time.

By the way, there is no scalar function corresponding to the TODAY function either.

If you want to use the current date and time or today's date in a QUERY function with Japan's time settings, I recommend combining it with the sheet functions NOW() and TODAY().

3-11. dateDiff() which returns the number of days between two dates

Within the query string of a QUERY function, you cannot perform subtraction between dates. That is where
returning the difference between two dates as a number of days

comes in handy.

It is similar to the sheet function DATEDIF, but be careful because the scalar function has two f's.

How to use the scalar function dateDiff()

Unlike the sheet function DATEDIF, the scalar function dateDiff cannot specify the unit in the third argument, so it can only return the difference in days.

Furthermore,

In contrast, the scalar function dateDiff() is,

In this way, not only is there no third argument, but the scalar function dateDiff() has the start date and end date reversed compared to the DATEDIF function.

In other words, if the start date is in the first column and the end date is in the second column,

This is how you use it.

Also, while the DATEDIF function returns a #NUM! error if the start date is later than the end date,

the scalar function dateDiff() can return a negative number of days. It's good that it doesn't result in an error.

For the record, dateDiff can be used not only with date types but also with date-time types, but

as stated in the official documentation, only the date portion of the value is used for calculations, and time values are truncated before comparison,

2025/06/30 0:01 and 2025/06/30 22:59 have a difference of almost 1 day when considering time, but

with dateDiff(), it becomes a comparison between 2025/06/30 and 2025/06/30 with the time truncated, so 0 is returned.

Although it is an important scalar function when handling dates in the QUERY function, I rarely see any sites that properly explain the QUERY function's dateDiff().

Q4. I want to extract tasks that are not "Completed" and have less than 3 days remaining until the deadline using the QUERY function

Now, let's try a challenge that utilizes dateDiff().

If you have a table like the one on the left, what kind of formula should you build with the QUERY function if you want to extract only data where the status (column 4) is not "Completed" and the deadline is less than 3 days away?

"Less than 3 days until the deadline" means the difference compared to today's date is less than 3.

In other words, if today is 2025/07/12, data with a deadline of 2025/07/15 is just outside the scope because the difference is 3 days, and data with a deadline of 2025/07/14 is subject to extraction because the difference is 2 days.

Please paste the following data into A1 and use it as a table named "Duty Roster". (Please adjust the deadline date according to the date you are attempting the challenge.)

担当	ToDo	期限	ステータス
山田	書類の提出	2025/07/14	着手
田中	電話する	2025/07/12	完了
佐藤	録画する	2025/07/11	未着手
山田	定期を買う	2025/07/18	完了
鈴木	まぜる	2025/07/16	着手
田中	フィルター掃除	2025/07/15	着手
鈴木	裏返す	2025/07/10	未着手
山田	感じる	2025/07/14	着手

Let's think about it!

↓↓
The answer starts here.
↓↓

A4. Extract tasks that are not "Completed" and have less than 3 days remaining until the deadline using the QUERY function.

Here is the answer.

I will explain.

First, since we want to exclude data where column 4 is "Completed", the first condition is

" where Col4 != 'Completed' "

In addition to this, we want to use "and" to set the difference from today's date to be less than 3 (<3), so this is where dateDiff comes in.

" where Col4 != 'Completed' and dateDiff(Col3, [Today's Date Here]) < 3 "

For today's date, we use the sheet function TODAY().

However, as we have learned so far, dates and date-times on a sheet cannot be used as-is within a query string.

To make them function as date literals, you need to combine them with the TEXT function and format them into the form of

date 'yyyy-MM-dd'

.

"where Col4 != 'Completed' and
dateDiff(Col3,date "&TEXT(TODAY(),"'yyyy-MM-dd'")&") < 3 "

Did you manage to do it?

Regarding this task, if you don't actually need to be that strict, you can use the scalar function now() that appeared earlier to write it simply like this:

It can be written simply like this.

If you use the scalar function now(), you can use it as a date-time as-is without worrying about literals, and furthermore, within dateDiff, the time portion of now() is truncated and only the date is adopted to return the difference, so it's great that

dateDiff(Col3,now()) < 3

allows now() to be used as-is.

However, this does not account for the 9-hour time difference, so while it returns the correct result from 9:00 AM Japan time until just before midnight, a date discrepancy will occur between 12:00 AM and 9:00 AM Japan time.

In the example above, when the Japan time is 2025/07/12 08:40:00, the now() function in the query returns 2025/07/11 23:40:00, which is 9 hours earlier.

Because dateDiff() calculates the difference based on a date that is one day before the Japanese date, it does not produce the correct result.

There is a discrepancy in the results between the top and bottom (data with a deadline of 2025/07/14 is missing).

This is why the scalar function now() can be difficult to use.

For now, dateDiff() seems quite useful!

3-12. Converting date-times or numbers to dates: toDate()

The final, 12th scalar function allows for type conversion from date-times or numbers to the date type.

.

It is a function somewhat similar to the sheet function TO_DATE function.

How to use the scalar function toDate()

The scalar function toDate() has two main roles.

1. Convert date-time data to a date

2. Convert numbers (epoch milliseconds) to a date

It can be said that it is a rare function within a query that can convert date-times or numbers into a different type called date.

Let's look at the first one, date-time to date type conversion, first.

For example,

You can use it in the select clause for date-time type columns 1 and 2 like this to truncate the time portion and convert it to a date type, or use it when applying conditions to date-time type data as a date in the where clause.

Q5. I want to extract data from Google Forms responses using the QUERY function (and its scalar functions) based on the date in the timestamp.

This is the same challenge as the previous one. Let's try it again using the scalar functions we learned this time!

The Google Forms responses are output as a table named Form Responses. The first column is a timestamp (date-time type).

In this case, what formula should I use in the QUERY function to extract rows where the date part of the timestamp matches the date entered in D1?

Please copy and paste the data below into cell A1 and use it as a table named "Form Responses".

タイムスタンプ	回答者
2025/02/25 15:11:08	田中さん
2025/03/11 15:21:50	佐藤さん
2025/03/11 15:27:40	山田さん
2025/03/13 15:19:06	鈴木さん
2025/03/13 15:23:57	小林さん
2025/03/13 15:24:20	加藤さん
2025/03/13 15:26:12	吉田さん
2025/03/13 15:26:39	中村さん
2025/03/14 7:51:17	木村さん
2025/03/19 15:12:12	斉藤さん
2025/03/19 15:18:41	松本さん
2025/03/19 15:18:51	井上さん
2025/03/19 15:19:11	林さん
2025/03/19 15:59:06	清水さん
2025/03/27 15:18:16	山口さん
2025/03/27 15:18:26	池田さん
2025/03/27 15:27:44	橋本さん
2025/03/28 12:14:04	前田さん
2025/03/28 14:09:52	鎌田さん

Let's think about it!

* If you were able to quickly create a formula using toDate(), try thinking of formulas that use other scalar functions as well!

↓↓
The answer is here.
↓↓

A5. Extracting data based on a timestamp date as a condition using the QUERY function (scalar function) from Google Forms response data

Here is the answer.

This is exactly the situation where you should use toDate()!

When written using comparison operators instead of scalar functions, it was

=QUERY(Form Responses[#ALL],
"where Col1 >= date "&TEXT(D1,"'yyyy-MM-dd'")&
" and Col1 < date "&TEXT(D1+1,"'yyyy-MM-dd'"))

This was very cumbersome, but by using toDate(), it became much cleaner.

By the way, as an alternative solution, you can use dateDiff() to treat date-times as dates and find the difference, looking for a difference of 0 to match the date, or

You can also use year(), month()+1, day() to check for matches in the year, month, and day respectively.

These are some possible formulas.

While the alternative solutions are just for fun, toDate(), which can convert date-times to dates in the QUERY function, is useful when handling Google Forms timestamps.

Make sure to remember this!

What are epoch milliseconds (UNIX time)?

Another role of toDate() is to convert numeric values into dates based on UNIX time (epoch milliseconds).

Officially,

“The epoch is defined as 00:00:00 GMT on January 1, 1970”

as stated, 1970-01-01 00:00:00.000 is 0, and the value counted from there with 1 millisecond (1/1000 of a second) as 1 is the epoch millisecond.

The function to convert this to a date type is toDate().

In other words, toDate(0) returns 1970/01/01, and one millisecond before the 24-hour mark converted to milliseconds,

24 * 60 * 60 * 1000

which is 24*60*60*1000-1, is the date-time 1970-01-01 23:59:59:999, so since toDate truncates the time portion, everything from 0 up to that point becomes 1970/01/01.

toDate( 24 * 60 * 60 * 1000 ) finally becomes 1970/01/02.

The digits are large, but it's similar to the concept of serial values.

This behavior is similar to the sheet function EPOCHTODATE function.

Many people may not be familiar with it, but the EPOCHTODATE function converts epoch seconds (or epoch milliseconds) into a date-time (based on UTC).

It is a relatively new function added to Google Sheets in February 2023, but it was overshadowed by powerful array manipulation functions added at the same time, such as LET and VSTACK, TOCOL, CHOOSEROWS, WRAPROWS.

Since it is in milliseconds, by specifying 2 as the second argument of EPOCHTODATE, it returns a date-time instead of a date, allowing for similar conversion.

By the way, while the EPOCHTODATE function cannot take negative values, the scalar function toDate() can convert negative epoch milliseconds into dates.

In other words, it can also handle dates before January 1, 1970.

Epoch milliseconds that can be used when processing dates with the QUERY function

“I don't use epoch milliseconds, so I don't need to remember them, right?”

Many people might think that, but if you can convert dates and date-times into epoch milliseconds (numbers), you can perform addition and subtraction using arithmetic operators, just like with serial values.

And it means that you can convert the resulting epoch millisecond value back into a date using toDate().

If you can do this, you can output a date that is X days after the date in a specified column using the QUERY function, or convert the scalar function now() into a Japan Standard Time date that accounts for the time difference!

However, unfortunately, there is no function provided that is the inverse of toDate() to convert dates or date-times into epoch milliseconds.

"So, what do we do?"

Unfortunately, this has become quite long, so let's tackle the rest of these advanced application examples next time.

*Although it is time-consuming and not very practical

Next time, advanced scalar function application examples and handling dates, date-times, and times with string comparison operators

This time, we learned 12 scalar functions for processing dates, date-times, and times.

Next time, the final part of the QUERY function date, date-time, and time series!

Advanced examples of date processing using scalar functions with epoch millisecond conversion and how to handle dates, date-times, and times using string comparison operators will be explained.