This tutorial explains how to properly hardcode DATETIME criteria within the FILTER function in Google Sheets.

The DATETIME (TIMESTAMP) values contain both date and time parts.

You can format it in several ways in Google Sheets using Format > Number > Custom date and time menu command.

Does that affect filtering?

I’ll come to that as it requires some explanation.

Some Google Sheets functions, such as the COUNTIF, accept DATETIME criteria as a string, for example, =countif(A2:A,">=16/01/2022 15:30").

In such a case, you must take care of the date formatting.

Enter =now() in cell A1 in a new tab in your current Sheet and follows that date format in the formula in that Sheet.

It can either be DD/MM/YY (Day-Month-Year) or MM/DD/YY (Month-Day-Year) format. You must adhere to that.

At present, the TIMESTAMP criterion as above doesn’t seem to work in the FILTER function in Google Sheets.

You require to additionally (virtually) format the range (array) also to the text.

Example:

=filter(A2:B, text(A2:A,"dd/mm/yyy hh:mm:ss")>"15/01/2022 15:30:00")

It may work! But when you want to hardcode DATETIME criteria within the FILTER function in Google Sheets, I recommend a different approach. Let’s go to that.

Sample Data (Employee Attendance)

Assume you have installed a fingerprint punching machine for employee attendance in your office/workplace.

What about finding the latecomers using the output (biometric time logs)?

Here is the attendance of six employees on two consecutive days.

Copy-paste the above attendance data in cell range A1:B13.

Before proceeding to the DATETIME FILTER formula, you may check whether the dates are correctly formatted in your Sheet. How?

1. Insert =day(A2) in any blank cell and check whether the returned number (day) is 15.
2. If not, enter =now() in another cell. Follow that output format to enter the ‘Time Logs’ manually in A2:A13.

Proper Way to Hardcode DATETIME Criteria within the FILTER Function in Google Sheets

Please scroll up and see one filter formula.

You can see that I have used there the TEXT function to virtually convert the data in the range A2:A to text/string and have used a TIMESTAMP text as the criterion/condition.

Here we won’t convert the range A2:A to string. Instead, we will use the date and time parts separately as the criterion.

Here is that formula.

FORMULA # 1

=filter(A2:B, A2:A>date(2022,1,15)+time(15,30,0))

See how I have hardcoded the DATETIME criterion, i.e., replacing text(A2:A,"dd/mm/yyy hh:mm:ss")>"15/01/2022 15:30:00" within this FILTER formula.

The above formula will filter the table if the TIMESTAMP in A2:A is greater than 15/01/2022 15:30:00.

The above explains the usage of DATETIME criteria within the FILTER function in Google Sheets.

Now we will apply the same in a few real-life scenarios.

Filter Latecomers Using Hardcoded DATETIME Criteria within the FILTER Function

Assume our office working time starts from 10:00 AM, and I want to find those punched after 10:05 AM on a particular day, i.e., on 15/01/2022.

Here we require to find the employees who have punched after 15/01/2022 at 10:05 AM and before 16/01/2022.

That means we require to use either the comparison operators or the ISBETWEEN function with the criteria.

I’ve already explained the use of ISBETWEEN within the FILTER function in Google Sheets.

Here let’s see how to use DATETIME criteria within ISBETWEEN and thus within the FILTER function in Google Sheets.

FORMULA # 2

=filter(A2:B, isbetween(A2:A,date(2022,1,15)+time(10,5,0),date(2022,1,16),false,false))

You may please check the time logs and names returned by the formula.

If you don’t like the ISBETWEEN, even though there is no such chance, you can try the below formula.

=filter(A2:B, A2:A>date(2022,1,15)+time(10,5,0),A2:A<date(2022,1,16))

I want to exclude the date part. I mean, what about finding the latecomers in the range irrespective of the date?

FORMULA # 3

=filter(A2:B, mod(A2:A,1)>time(10,5,0))

This FILTER formula will return the list of employees who are late on 15/01/2022 and 16/01/2022.

The MOD function in the formula removes the date and just returns the time part.

Conclusion

The easiest way to use TIMESTAMP criteria is by entering them in cells and referring to them in the formula.

The above method is just for those who wish to hardcode the TIMESTAMP criteria within the FILTER function.

Here are some related resources.

• How to Filter Timestamp in Query in Google Sheets.
• How to Use Timestamp within IF Logical Function in Google Sheets.
• Extract the Earliest or Latest Record in Each Category Based on Timestamp in Google Sheets.
• How to Use DateTime in Query in Google Sheets.
• How to Compare Time Stamp with Normal Date in Google Sheets.
• Convert Unix Timestamp to Local DateTime and Vice Versa in Google Sheets.
• How to Increment DateTime by One Hour in Google Sheets (Array Formula).
• How to Extract Date From Time Stamp in Google Sheets.