0

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.

  • Please share a spreadsheet that does not require a request for access. – Tedinoz Nov 13 at 6:35
  • The problem was that the second part of the array sometimes produces no results. I edited the formula to check if that's the case and act accordingly. I've added a new sheet with the fix applied for anyone that's interested. – Alec McCartney Nov 15 at 8:40
  • It looks as though you have answered your own question. Would you please post it as an answer so that others can benefit from your experience. – Tedinoz Nov 16 at 23:24
  • It looks as though you have answered your own question. Would you please post it as an answer (with a snapshot of the output) so that others can benefit from your experience. Since your original question mentioned a problem when "the start date is 19/11/20 19/11/20 or greater", you might care to ensure that your solution includes a start date of 19/11/20 or greater. – Tedinoz Nov 16 at 23:52
  • With the benefit of hindsight, I note that line 117 of "All Tranches":"LAINDON, 18/11/2020, Josh, 18/11/2020, Gerry" is the last line before any data with dates of 19/11/2020 and greater AND it has values in both columns "I and J" and "K and L". Presumbaly this was the culprit in your original formula and it has been resolved in your revised formula. – Tedinoz Nov 16 at 23:52
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 enter image description here

| improve this answer | |
0

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

Query output

| improve this answer | |
  • I altered the sheet name and date formatting, but couldn't get this one to work. It returns #VALUE in A3 and A4. Did I miss something else that I should change? I left it on the (fixed) fixed sheet of the live worksheet. – Alec McCartney Nov 17 at 8:06
  • You have used a text date format of "dd-mm-yyyy" (for example: text(B1,"dd-mm-yyyy"). I know that this seems logical and sensible (we both format our dates that way!; the data is formatted that way too!), but... it's wrong. The date format MUST be "yyyy-mm-dd" (such as text(B1,"yyyy-mm-dd")). It's one of those don't-try-and-find-the-logic-just-do-it things. Change both the text date formats and you'll be right. – Tedinoz Nov 17 at 9:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.