# Formula to identify the task name of next child task due

✭✭✭✭✭

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)))

Tags:

• ✭✭

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.

• ✭✭

Hi Kayla,

Let me know if this helps.

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.

• ✭✭✭✭✭

@Nick055 thanks for being willing to help!

For some reason, this is returning "0"

• ✭✭
edited 03/16/24

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.

• ✭✭

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.

• ✭✭✭✭✭

That worked - you're a genius!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!