In my spreadsheet, the first sheet has a column with text entries alongside their corresponding date and time. On the second sheet, I want to apply a filter to find specific text elements (utilizing a formula like “=filter(sheet1!A1:A, search(“search term”, sheet1!A1:A))”) but restrict the results to those where the date and time fall within the range of 18 hours to 3 days prior to the current date and time. Additionally, there is only one data column besides the date and time, which appears formatted as “Date: 24.03.2013 11:04”.
Hey Emma, yes, it’s certainly possible to filter your data based on the current date and time in Google Sheets. From what you’ve described, you can combine the FILTER function with the QUERY function to achieve this.
The QUERY function allows you to select and filter data based on dates by utilizing Google Sheets’ built-in date functions. You’ll need to use date and now functions inside the QUERY to dynamically restrict the range to 18 hours and 3 days beforehand. This might be slightly tricky depending on your date format, but converting your date format into a universally recognized format (like YYYY-MM-DD) beforehand could make things easier.
You can also consider using a helper column that computes the time difference between each entry’s timestamp and the current time. You can use the formula =NOW() - A2 in your helper column (assuming A2 is where your date starts), which calculates the difference in days. Then on your second sheet, combine this helper data with the FILTER function to select entries where the computed difference is between 0.75 (18 hours) and 3. This approach ensures your data is handled smoothly without overly complicating single formulas.
One approach you could try involves using the ARRAYFORMULA function in combination with some date arithmetic to create a dynamic filter. You can calculate the lower and upper bounds of your desired time range directly in your formula, then use these to filter your data. To transform your timestamps into a comparable format, you might need to convert them into serial numbers using DATEVALUE and TIMEVALUE functions, if needed. This way, everything updates automatically based on the current date and time, without needing a separate helper column.