Do you want to become a master at using DATETIME criteria in the FILTER function in Google Sheets? Look no further! In this tutorial, I will guide you through the process of effectively hardcoding DATETIME criteria to filter data accurately.
A Closer Look at DATETIME Values
Before we dive into the nitty-gritty, let’s understand what DATETIME (TIMESTAMP) values are all about. These values combine both date and time elements, making them versatile for various applications. In Google Sheets, you can customize the formatting of DATETIME values by navigating to Format > Number > Custom date and time.
You may wonder if this formatting affects filtering. The answer is both yes and no. While some Google Sheets functions, like COUNTIF, accept DATETIME criteria as strings (e.g., =countif(A2:A, ">=16/01/2022 15:30")), you need to be cautious with the date formatting.
To ensure accurate results, enter =now() in cell A1 of a new tab and use that date format in the formula within the same tab. Adhering to this format, which can either be DD/MM/YY or MM/DD/YY, is crucial.
Currently, the TIMESTAMP criterion mentioned above doesn’t seem to work in the FILTER function in Google Sheets. To address this, you need to virtually format the range (array) as text.
Here’s an example:
=filter(A2:B, text(A2:A,"dd/mm/yyyy hh:mm:ss") > "15/01/2022 15:30:00")
While this approach may work, I recommend an alternative method for hardcoding DATETIME criteria within the FILTER function. Let’s explore that now!
Using Hardcoded DATETIME Criteria in FILTER Function
Suppose you have installed a fingerprint punching machine to track employee attendance in your office. You can utilize the FILTER function to identify latecomers based on the biometric time logs.
Here’s the attendance data of six employees over two consecutive days:
Copy and paste this attendance data into cells A1:B13. Before proceeding with the DATETIME FILTER formula, ensure that the dates are correctly formatted in your sheet. You can do this by following these steps:
- Insert the formula
=day(A2)in any blank cell and check if the returned number (day) is 15. - If not, enter
=now()in another cell and manually enter the ‘Time Logs’ output format (YYYY-MM-DD HH:MM:SS) in cells A2:A13.
Now, let’s delve into the proper way of hardcoding DATETIME criteria within the FILTER function.
Proper Method for Hardcoding DATETIME Criteria
Take a moment to review the FILTER formula above. Notice that I used the TEXT function to convert the data in range A2:A to text/string and set a TIMESTAMP text as the criterion/condition. However, in this approach, we won’t convert the range A2:A to a string. Instead, we will use the date and time elements separately as criteria.
Consider the following formula:
=filter(A2:B, A2:A > date(2022,1,15) + time(15,30,0))
Observe how I hardcoded the DATETIME criterion, replacing text(A2:A,"dd/mm/yyyy hh:mm:ss") > "15/01/2022 15:30:00" within this FILTER formula. This specific formula filters the table if the TIMESTAMP in A2:A is greater than 15/01/2022 15:30:00.
So far, we have explored using DATETIME criteria within the FILTER function in Google Sheets. Now, let’s apply these concepts to real-life scenarios.
Filtering Latecomers
Suppose our office working hours start at 10:00 AM, and we want to identify employees who have punched in after 10:05 AM on a particular day, such as 15/01/2022. To achieve this, we need to find the employees who have punched in after 15/01/2022 at 10:05 AM but before 16/01/2022.
One option is to use either comparison operators or the ISBETWEEN function with the criteria. The use of ISBETWEEN within the FILTER function has been explained in previous articles. Here, we will explore how to utilize DATETIME criteria within ISBETWEEN and the FILTER function in Google Sheets.
Consider this formula:
=filter(A2:B, isbetween(A2:A,date(2022,1,15) + time(10,5,0),date(2022,1,16),false,false))
To visualize the results, refer to the time logs and names returned by the formula.
Even though there is little chance you won’t like ISBETWEEN, we provide an alternative formula:
=filter(A2:B, A2:A > date(2022,1,15) + time(10,5,0), A2:A < date(2022,1,16))
If you wish to exclude the date part and find latecomers within a range, regardless of the date, a formula like this will suit your needs:
=filter(A2:B, mod(A2:A,1) > time(10,5,0))
This FILTER formula returns a list of employees who arrived late on both 15/01/2022 and 16/01/2022. The MOD function in the formula removes the date and returns only the time part.
Final Thoughts
While the easiest way to use TIMESTAMP criteria is by entering them in cells and referring to them in formulas, some individuals prefer hardcoding the criteria within the FILTER function. The approach outlined in this article caters specifically to such individuals.
For further reading, consider exploring the following 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
Mastering the art of hardcoding DATETIME criteria within the FILTER function will elevate your Google Sheets skills to greater heights. Happy filtering!
