Next earliest date after today

Chris Carroll

Need help with a formula for this. I have a column of end dates. It should be showing this 8/12 date but I cant get it to show up.

MCorbin

What is the logic you're trying to drive towards?

Are you trying to find the next earliest date, that's after today, in the list that is in the green column?

If so, here's what I came up with:

=MIN(COLLECT([Finish Date]1:[Finish Date]42, [Finish Date]1:[Finish Date]42, >TODAY()))

MCorbin

What is the logic you're trying to drive towards?

Are you trying to find the next earliest date, that's after today, in the list that is in the green column?

If so, here's what I came up with:

=MIN(COLLECT([Finish Date]1:[Finish Date]42, [Finish Date]1:[Finish Date]42, >TODAY()))

Chris Carroll

Yes that was perfect! Sorry I couldnt describe it better. I do have a question. why do you have to input the range twice? That's where I was getting it messed up, I was only inputting it once. everything else, I was on the right path.

MCorbin

Because the function (Collect) syntax requires it....

When you set up the function, you first tell it the range you want it to collect (in this case, the date column). Then it wants to know your criteria for which ones you want to collect - but you have to tell it the range that your criteria applies to first, and then the actual criteria.

A lot of times, the range your collecting and the range you're using for criteria are the same, as with this formula, but your formula doesn't know that, so you have to tell it.

And they're not always the same, so it gives you the flexibility to collect data even when that particular data element isn't part of the criteria.

(Hopefully that makes sense)