Next earliest date after today
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.
Best Answer
-
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()))
Answers
-
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()))
-
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.
-
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)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 466 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!