I’m working with a Google Sheets document that has two sheets. The first sheet has a column with text content and another column showing dates and times in this format: “Date: 15.07.2024 14:30”.
On the second sheet, I want to create a filtered view that does two things:
Finds rows containing specific text using something like =filter(sheet1!B1:B, search("keyword", sheet1!B1:B))
Only shows results where the timestamp falls within a certain window - specifically between 18 hours ago and 3 days ago from right now.
I can handle the text filtering part but I’m stuck on how to add the time-based condition. Is there a way to combine both filters so I get results that match my search term AND fall within that specific time range?
hey, try this: =filter(sheet1!A1:B, (search("keyword", sheet1!B1:B)) * (datevalue(mid(sheet1!B1:B,7,10)) <= today()-18/24) * (datevalue(mid(sheet1!B1:B,7,10)) >= today()-3)). just adjust the mid part if ur date format’s different.
You need to extract both date and time from your timestamp string to get that 18-hour window right. Since your format is “Date: 15.07.2024 14:30”, you’ll need to do some text manipulation first.
The trick is combining datevalue and timevalue functions to handle your full timestamp. The mid functions grab the date part (characters 7-16) and time part (characters 18-22) separately, then combine them for comparison against your time window. Just double-check that those character positions match your actual data format.
Been dealing with the same date filtering headaches for years. Google Sheets formulas turn into a mess when you’re parsing custom dates and doing time calculations.
Skip the nested MID and DATEVALUE nightmare - set up automation instead. It’ll monitor your sheet, extract timestamps correctly, handle date ranges without formula chaos, and keep your filtered sheet updated automatically.
Best part? It runs in the background so your second sheet stays current. No more formula errors when formats change or manual updates. You can add complex filtering later without creating formula soup.
I’ve built workflows that pull sheet data, apply multiple filters (including time-based), and push clean results back. Way more reliable than forcing Google Sheets to do heavy lifting it wasn’t built for.