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.
Best Answer
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.
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