Google Sheets - Using query or VLOOKUP to populate list of rows with date range that overlaps specified period/date range

1
votes

I'm trying to create a list of rows another sheet based upon whether or not the date range in each row overlaps with a specified date range (in this case a calendar month. Each of the rows represents an individual entity, and I'm looking to list each of those entities in another sheet based upon whether or not that entity has activity in a particular month.

Here's the sheet were I've created the test data: https://docs.google.com/spreadsheets/d/1epCnivFZvxjPNBdNK82Np0aPfFN1LbIXHrrbgnQEQus/edit?usp=sharing

I've built this formula (in cell B9) in the second tab (Burn), to check whether the entries in the first tab (Weekly) have overlap with the date range specified by cells I5 and J5:


=QUERY(Weekly!B7:K99,"select E,B,C,I,J,D,H,G where I >= date '"&TEXT(G7,"yyyy-mm-dd")&"'")

This isn't correct however, as it also includes rows where the start date of the entity is after the specified month range.

I've realized that the way to do this is to check for overlaps between the required monthly date range, and each of the entities and then create a list from there, using a formula like this:


=SUMPRODUCT(($I$5<=Weekly!$J$7:$J)*($J$5>=Weekly!$I$7:$I))>1

My problem, is that I've so far failed to bring this into a query, or a list function that hasn't blown up.

Any advice would be greatly appreciated!

1
dategoogle-sheetsgoogle-sheets-formula

1 Answers

1
votes

Never mind, I figured it out myself. For reference, here is the approach I took:


=iferror(FILTER(Weekly!B7:J,(($G$5<=Weekly!$J$7:$J)*($H$5>=Weekly!$I$7:$I)>0)),"Error message goes here. ")

I abandoned trying to use QUERY, as the date handling is quite cumbersome. VLOOKUP was probably possible, however it was far too complicated. FILTER works really well and provides a simple formula.

Thanks!