CODEWITHSUNDEEP
google-sheets
Kelly Cheatham
Kelly Cheatham

Reputation: 13

Google Sheets Query or Filter Function based on multiple timestamp ranges

I am hoping to take a large set of data that has been populated via Google Form and filter/query it so that I only see submissions between specific date/time ranges. I have been able to filter and query for ONE timestamp range (e.g. from 9/1/2020 15:00 - 9/2/2020 9:00), but am having trouble adding a second range.

Here's a sample spreadsheet with a small data set. I would like to get Sheet2 to populate with only the submissions between 8/31/2020 15:00:01 to 9/1/2020 8:59:59 AND 9/1/2020 15:00:01 to 9/2/2020 8:59:59 AND 9/4/2020 15:00:01 to 9/7/2020 8:59:59.

https://docs.google.com/spreadsheets/d/1UTYXaIoZNAINcGTRGozlLfvf9eMabLg1ud2YcPtF9l0/edit?usp=sharing

(I need to capture submissions that were done outside of contract hours (evenings and weekends), so that's why I need to pull just those ranges.)

Upvotes: 1

Views: 337

Answers (1)

Erik Tyler
Erik Tyler

Reputation: 9345

I've added a sheet ("Erik Help") with the following formula in A1:

=ArrayFormula({"Email","Timestamp","Score";FILTER(Sheet1!A:C, ((Sheet1!B:B>(DATEVALUE("8/31/2020")+TIMEVALUE("15:00:00")))*(Sheet1!B:B<(DATEVALUE("9/1/2020")+TIMEVALUE("9:00:00")))) + ((Sheet1!B:B>(DATEVALUE("9/1/2020")+TIMEVALUE("15:00:00")))*(Sheet1!B:B<(DATEVALUE("9/2/2020")+TIMEVALUE("15:00:00")))) + ((Sheet1!B:B>(DATEVALUE("9/4/2020")+TIMEVALUE("15:00:00")))*(Sheet1!B:B<(DATEVALUE("9/7/2020")+TIMEVALUE("15:00:00")))), Sheet1!C:C>7)})

I think it will make more sense when you see how it is broken up in the formula bar of the sheet itself (Stack breaks things differently here).

This one formula creates the three headers and then all results.

FILTER is the key here. To help you better understand what may look confusing at first, I'll display the same formula a different way below:

=ArrayFormula({"Email","Timestamp","Score";

FILTER(Sheet1!A:C, including only those rows where

((this is true))AND(this is true)) OR ((this is true))AND(this is true)) OR ((this is true))AND(this is true)),

and

Sheet1!C:C>7)})

Upvotes: 1

Related Questions