Return date in Child Row where Status is not Complete

Amanda Merrigan

I'm wondering if someone can help me with a formula or solution.

I would like the Target Finish Date (circled in red) to display the target finish date in the next subtask that is due. (Note that dependencies are not enabled here)

Per the example below, the next item due below is "Design for Production" with a date of January 15. I would like January 15 to show display on the parent line so that when viewing card view the next due date for the entire task is visible.

Thanks!

Neslihan Kalender

Not sure if this the most effective solution but you can try this,

need one more column to get the dates if status is not complete, then take the min date of this range:

Amanda Merrigan

We figured it out. We put a =MIN(Children()) in the Next Task Due Date Parent and then referenced the parent line only in the formula, not the column range.

Amanda Merrigan

I should note that it can be based on the status being "complete", or by the Finish Date (Actual) column being populated - both would shown in real time.

Neslihan Kalender

Not sure if this the most effective solution but you can try this,

need one more column to get the dates if status is not complete, then take the min date of this range:

Amanda Merrigan

@Neslihan Kalender - any idea how I could do this when the number of rows I have are not always 6? Without having to manually change that finish date formula every time of course ;)

For example, sometimes we may not need to submit for approval so we would only need Assessment, Design for Production and Issue for Production. In this case I would only need =MIN([Column4]2:[Column4]4). But I have the 2:7 formula in my template which will pick up the 6 rows below where I paste it.

Amanda Merrigan

We figured it out. We put a =MIN(Children()) in the Next Task Due Date Parent and then referenced the parent line only in the formula, not the column range.