1

I have a range formula on a Google Sheet that pulls the next future date out from a column containing nothing but dates (column C), starting from the 4th row:

=MIN(FILTER(ARRAYFORMULA(C4:C),C4:C>TODAY()))

This works, but the problem now is that there's a few rows of data within the range that I cannot remove (i.e. they need to stay there) but not be counted for the purposes of this cell's calculation. So far, the best I have come up with is this:

=MIN(FILTER(ARRAYFORMULA(C4:C),C4:C>TODAY(),C4:C<>C58:C64)) (rows 58 to 64 are the ones I want to exclude)

This formula doesn't throw any errors but I can see it's wrong - the first formula (without the exclusion) shows a value of December 7th for the next upcoming date (which is correct); where the second formula shows December 13th, which while an item on the spreadsheet, is something like the 3rd or 4th date in sequence.

What am I doing wrong here?

1

1 Answer 1

0

Use row() and isbetween(), like this:

=min(filter(C4:C, C4:C > today(), not(isbetween(row(C4:C), 58, 64))))

See row() and isbetween().

1
  • Amazing, that works. Thank you! Nov 15, 2023 at 14:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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