0

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?

Improve this question
1

1 Answer 1

Reset to default
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().

Improve this answer
1
  • Amazing, that works. Thank you!
    – Chris Douglas
    Nov 15 at 14:22

Your Answer

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

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