Formula to identify the task name of next child task due

Kayla Q

I'm trying to get a formula to work that will show me, in a specified row, the Task Name of the child task that is due next.

Formula: =IF(AND(COUNT(ANCESTORS([Task Name]@row)) > 0, isParent@row = 1), IF(COUNTIFS(CHILDREN(Status@row), "Complete") < COUNT(CHILDREN([Task Name]@row)), INDEX(COLLECT(CHILDREN([End Date]@row), CHILDREN(Status@row), @cell <> "Complete", CHILDREN([End Date]@row), ISDATE(@cell), CHILDREN([End Date]@row), @cell = MIN(@cell)), 1)))

Nick055

Try this & let me know if it fixes it!

=INDEX([Task Name]:[Task Name], MATCH(MIN(COLLECT([Target End Date]:[Target End Date], [Target End Date]:[Target End Date], ISDATE(@cell), Status:Status, <>"Complete", Level:Level, =2)), [Target End Date]:[Target End Date], 0))

I assume Target End Date is the column to get the latest due date from. If that's not the case, just be sure to update that in the formula to the column it is based on.

Nick055

Hi Kayla,

Let me know if this helps.

=MIN(COLLECT([Task Name]:[Task Name], [End Date]:[End Date], ISDATE(@cell), Status:Status, <>"Complete", [Level]:[Level], =2))

This formula will check ALL Level 2 rows with an end date that isn't marked as complete & return the task name with the earliest date.

If there is another condition that needs to be narrowed down, we'll need to add another condition to the Collect function.

Kayla Q

@Nick055 thanks for being willing to help!

For some reason, this is returning "0"

Nick055

Of course!

And I see what I did wrong, the min function is for numbers / dates & your task name is text! Let me see how to fix that.

Nick055

Try this & let me know if it fixes it!

=INDEX([Task Name]:[Task Name], MATCH(MIN(COLLECT([Target End Date]:[Target End Date], [Target End Date]:[Target End Date], ISDATE(@cell), Status:Status, <>"Complete", Level:Level, =2)), [Target End Date]:[Target End Date], 0))

I assume Target End Date is the column to get the latest due date from. If that's not the case, just be sure to update that in the formula to the column it is based on.

Kayla Q

That worked - you're a genius!