Find next milestone in project plan
I have a project plan in which I want to write a formula to retrieve the next milestone, a task with a duration of zero, from a list of tasks.
Task Name = Text/Number column
Duration = Duration type column
Start = Date column
I have this formula which returns the first milestone in the Task Name list. The first task with a duration of zero.
=INDEX([Task Name]:[Task Name], MATCH(MIN(Duration:Duration), Duration:Duration, 0))
However, I want the first milestone not in the past, so I need a date parameter. I have tried the following but I always get an error.
=INDEX([Task Name]:[Task Name], MATCH(AND(MIN(Duration:Duration), Start:Start - TODAY() > 1, 0), Duration:Duration, 0)))) #INVALID DATE TYPE
=INDEX([Task Name]:[Task Name], MATCH(AND(MIN(Duration:Duration), Start:Start > TODAY(), 0), Duration:Duration, 0)))) #INVALID DATE TYPE
=INDEX([Task Name]:[Task Name], MATCH(AND(MIN(Duration:Duration), MIN(Start:Start @cell), Duration:Duration, 0))) #UNPARSEABLE
=INDEX([Task Name]:[Task Name], MATCH(AND(MIN(Duration:Duration), =MIN(COLLECT(Start:Start, Start:Start, @cell > TODAY())), Duration:Duration, 0))) #INCORRECT ARGUMENT SET
I have tried many other variations of this with no success.
I’ve searched the forums and have seen people with similar questions, but have not seen an answer yet. Is this possible?
Thank you,
Brent.
Answers
-
Try something like this:
=INDEX(COLLECT([Task Name]:[Task Name], Duration:Duration, @cell = 0, Start:Start, @cell> TODAY()), 1)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!