Find next milestone in project plan

dBrent

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.

Paul Newcome

Try something like this:

=INDEX(COLLECT([Task Name]:[Task Name], Duration:Duration, @cell = 0, Start:Start, @cell> TODAY()), 1)