[Break] Filtering Google Form responses by date (Timestamp extra edition)

The casual chat articles updated on either Saturday or Sunday get more page views, perhaps because they cover the latest topics, but this is the main article.

The latest casual chat article is here ↓

In the previous articles, I introduced the automatic timestamp function that can be used in Google Sheets over a three-part series.

The previous main article is here ↓

As a short break before moving on to the next series, I thought I would insert a light topic related to timestamps that can be completed in one go.

This is the timestamp extra edition.

Q. I want to specify a date and filter responses from Google Forms output to a spreadsheet so I can view them on another sheet.

I don't use timestamps, but I do use Google Forms. Aren't there many people who say that?

Perhaps one of the most common timestamps you encounter is the one automatically added to column A when you export responses from Google Forms to a spreadsheet.

The requirement this time is to filter this by date.

I want to make it look like this below

Timestamps are a very convenient feature because they automatically record the date and time the respondent answered, but they record not just the date, but also the hours, minutes, and seconds.

This is also appreciated, but it is also a point where beginners stumble when they want to see only the data for a specified date (for example, today) on a separate sheet.

They try writing a formula that seems right, but it doesn't extract correctly because it contains the time... and eventually, on sites like Yahoo! Chiebukuro, you see things like ↓

Like this, there are quite a few people who ask questions that the reader cannot understand at all.

Some people try to extract only the date part by adding a column and using a function for extraction, but when a new form response comes in, they fall into a pattern where the function they entered is not applied! as well.

Regarding the application of functions when new form responses are received, it can be solved from the beginning by using an array formula with Arrayformula, but in this case, you are just filtering by date, so there is no need to prepare a work column.

So, which function should you use?

↓Answer from here

A. If you want to filter by a specified date, the FILTER function + INT function is the best.

Speaking of the two giants of functions that filter data ranges or arrays by conditions in Google Sheets,

• FILTER function

• QUERY function

are the ones.

You can extract by filtering only by date from the timestamp using either function, but this time, I recommend using the FILTER function.

There are three reasons:

1. FILTER spills without using ARRAYFORMULA

2. It is troublesome to write when using a date as a condition in the QUERY function

3. Since you are just simply narrowing down conditions, there is no need to use QUERY

Let's check the formula and how it works before going into the explanation.

//A4に入れる式
=FILTER('フォームの回答 1'!A2:C,INT('フォームの回答 1'!A2:A)=B1)

Please enter the titles in the title row (A3:C3 on Sheet 2) beforehand. Since the function will expand automatically, you just need to enter it in cell A4 and you're good to go.

This time, since I want to set the condition to check if only the date part of the date-time data matches the specified date in B2, I am combining it with the INT function.

Reason for using the INT function

This requires an understanding that

INT is a function that truncates the decimal part

, and that date data is actually a numerical value called a

serial value, where
1 day (=24 hours) is 1, and anything less (the hour:minute:second part) is treated as a decimal

.
I will also explain this part in the next topic.

When comparing

date data (2022/09/01) with
date-time data (2022/09/01 09:02:20) like this,

2022/09/01 is considered to be 2022/09/01 00:00:00.
In short, it's the exact moment it becomes September 1st, 0 hours 0 minutes 0 seconds.

Therefore
2022/09/01 is not equal to 2022/09/01 09:02:20, but
2022/09/01 =< 2022/09/01 09:02:20 holds true.

If you want to check if the date parts are the same, you need to remove the
09:02:20 part (the decimal part of the serial value) of 2022/09/01 09:02:20. This is the role of INT.To put it simply,


if you put a timestamp (date-time) into INT, it becomes just the date

that's what it means.

You're okay with this, right?

1. FILTER spills even without using ARRAYFORMULA

Since the INT function is a function that normally works on a single cell,
=INT( A2 )
works, but
=INT( A2:A )
results in an error. (The latest version of Excel spills automatically.)

If you want to apply it to a cell range like A2:A, in Google Sheets, you need to combine it with Arrayformula like
=ARRAYFORMULA( INT( A2:A ) )
as shown above.

However, when combined with the FILTER function used this time or other functions that handle arrays, ARRAYFORMULA is not needed, and the processing is automatically applied to the range. This makes the description much shorter.

By the way, the QUERY function is unfortunately special, and when performing array processing that combines functions with ranges or results, ARRAYFORMULA is required.

(Side note) If you want to include the title row in the FILTER formula

If you want to include the title row (A3:C3 on Sheet 2) in the FILTER function,
you can simply add the condition "or it is the first row" to the existing conditions as shown below.

A point to note is that AND and OR cannot be used in array processing, so
for the "or" (OR) part, use +.

*By the way, for "and" (AND), use * (multiply) as a substitute.
This is based on the concept that FALSE is 0 and 1 or more is TRUE. I will touch on this in another article if the opportunity arises.

So, with that said, you can include the title row by using a formula like this.

//A3に入れる式
=FILTER('フォームの回答 1'!A:C,(INT('フォームの回答 1'!A:A)=B1)+(ROW('フォームの回答 1'!A:A)=1))

Of course, you can also replace the reference to cell B1 this time with TODAY() directly to automatically display "only today's data".

2. It is cumbersome to use dates as conditions in the QUERY function

The QUERY function has unique conventions, and writing conditions when handling dates is cumbersome.

Since you cannot use the B1 reference as is, you need to format the date data in cell B1, which serves as the condition, into a format that the QUERY function can use.
It looks something like this.

The explanation for this part is well-covered by "Itsumo Tonari ni IT no Oshigoto", which is a bible-like resource for GAS and sheet function beginners.

However, since column A contains extra hours, minutes, and seconds, you cannot simply use an equals sign. One more trick is required.

When writing in QUERY

//1.不等号ではさみうち
=Query('フォームの回答 1'!A:C,"where A >= date '"&TEXT(B1,"yyyy-MM-dd")&"' AND A < date '"&TEXT(B1+1,"yyyy-MM-dd")&"'")


//2.toDate() で日付に変換
=Query('フォームの回答 1'!A:C,"where toDate(A) = date '"&TEXT(B1,"yyyy-MM-dd")&"'")

For descriptions that can be used with the Query function around here, "Keiei Kanri de Programming" is very detailed.

By the way, regarding the sandwiching method in the first description, the latter half is

I have set it as. This +1 is necessary when specifying an end date.

When using the FILTER function to extract data by specifying a start date and end date for a timestamp column as in this case, +1 is also required for the end date.

As explained earlier, when filtering date and time with a date and inequality sign, if the end date B1 is, for example, 2022/09/01, this represents 2022/09/01 00:00:00.

In other words, if you write the condition as "less than end date 2022/09/01", then
2022/09/01 07:30:10 and 2022/09/01 10:15:32 and other 9/1 data will
all be excluded.

Ideally, if the end date is 2022/09/01, you would want data up to 2022/09/01 23:59:59.

Therefore, by using +1 to make it the next day, and then writing the condition "less than that date" in the latter half and connecting it with AND, in this case...

Greater than or equal to 2022/09/01 00:00:00 AND less than 2022/09/02 00:00:00
A >= 2022/09/01 AND A < 2022/09/02
↓ In other words
The date is 2022/09/01

is what I have set.

3. Since it's just a simple condition filtering, there's no need to use QUERY

As for the final reason, this is true; the true value of QUERY, the most powerful function representing Google Sheets, lies in creating summary tables through grouping and pivoting.

If you are just filtering data as it is, like in this case,

A small fry like this isn't worth the attention of the great QUERY himself.
We'll take care of it. Yee-haw! (Sounds like a henchman lol)

That is the reason.

Personally, I think FILTER is also a powerhouse in the Four Heavenly Kings class, so I don't think it would say such small-fry lines, but...

Anyway, for cases like this, processing with other simple functions is sufficient.

(Aside) Cases where it is better to use the QUERY function

If the requirement this time was not to output to a different sheet within the same spreadsheet, but to
output to another spreadsheet, then the story changes.

The reason is that the importrange function appears, making the description long.
With FILTER, re-describing importrange is also required in the condition part, resulting in a very long and complicated formula.

In cases involving importrange, one of QUERY's cheat abilities, "simplifying the specification of columns to extract and columns to target for conditions as Col1, Col2", is very effective.

It will come up frequently when talking about functions in the future, but anyway, QUERY is overwhelmingly strong when it comes to virtual array manipulation.

However, by combining it with the latest LAMBDA, it will become possible to achieve this with simple formulas that eliminate wasteful repeated descriptions even with other functions in the future.

It has become long, but that was the "short tip" extra edition related to timestamps, extracting by date in Google Forms.

Connecting to date-related topics, next time I think I will take up function calendar.

■ Next article in this series