Google Sheets FILTER function produces no result after certain start date

Question

I have a sheet that is supposed to show which people are working where between a start date and an end date.

The FILTER() function is in cell A3.

=IFERROR(SORT(
              {
               FILTER({'All Tranches'!C:F, 'All Tranches'!I:J}, 'All Tranches'!I:I>=$B$1, 'All Tranches'!I:I<=$D$1
                     );
               FILTER({'All Tranches'!C:F, 'All Tranches'!K:L}, 'All Tranches'!K:K>=$B$1, 'All Tranches'!K:K<=$D$1
                     )
              }
              , 5, TRUE
             )
         )

The start date is in B1 and the end date is in D1. 'All Tranches' is a sheet that aggregates data from three other sheets. Columns I and K of 'All Tranches' contain dates. If I set the start date to anything before 19/11/20 and set the end date to anything after that, the filter returns the results that I would expect. If I set the start date to 19/11/20, or any date after that, I get no results. I've been messing around with making sure all the source dates are formatted as dates and I've tried using TO_DATE() inside the formula, but it hasn't worked.

A copy of the spreadsheet is here: https://docs.google.com/spreadsheets/d/1pASU-YhvgfLCwM10er4o7fLerOfiPiVk7aFsPkvTHS0/edit#gid=663076104

Why doesn't it work when the start date is 19/11/20 or greater? That date doesn't seem to have any significance in my data.

Answer 1

The problem was that, as the second column of dates doesn't always contain data, if the second FILTER produced no results, the array would return an error, which was suppressed by the IFERROR, so the cell gave no results at all. I fixed it by first making a check to see if the second FILTER would produce any results.

=IFERROR(IF(COUNT(FILTER('All Tranches'!K:L, 'All Tranches'!K:K>=$B$1, 'All Tranches'!K:K<=$D$1))=0,

SORT(FILTER({'All Tranches'!C:F, 'All Tranches'!I:J}, 'All Tranches'!I:I>=$B$1, 'All Tranches'!I:I<=$D$1), 5, TRUE),

SORT({FILTER({'All Tranches'!C:F, 'All Tranches'!I:J}, 'All Tranches'!I:I>=$B$1, 'All Tranches'!I:I<=$D$1);
FILTER({'All Tranches'!C:F, 'All Tranches'!K:L}, 'All Tranches'!K:K>=$B$1, 'All Tranches'!K:K<=$D$1)}, 5, TRUE)))

Alternatively, I think I could have moved the IFERROR function to directly wrap each FILTER, rather than wrapping the array, but as this fixed worked, I didn't try it.

Output

Answer 2

You want to create a filter to select transactions that occur between two given dates. Your data contains dates in two columns, so your output should reflect results both columns.

This answer uses a QUERY and is offered as an alternative.

=SORT(query({query(data!A1:L,"select C, I, J WHERE I >= date '"&text(B1,"yyyy-mm-dd")&"' AND I <= date '"&text(D1,"yyyy-mm-dd")&"'");query(data!A1:L,"select C, K, L WHERE K >= date '"&text(B1,"yyyy-mm-dd")&"' AND K <= date '"&text(D1,"yyyy-mm-dd")&"'")}),2,TRUE)

Logic

• There are two queries, one for each column date.
  • these are nested so that the output appears as a single stack of three columns.
• SORT is applied to the nested queries, on the date column.

---

Query Output