Cell formula that will highlight a task expired if any of its sub tasks are expired

KTech · 06/05/19

Hello All,

I'm trying to create a cell formula that will highlight a task expired if any of its sub tasks are expired.

For example if a parent task has any child tasks with due dates earlier than the one of the parent it should be marked as expired.

Is there a formula available for this?

Thanks

Paul Newcome · 06/05/19

Try something like this in the parent row...

=IF(JOIN(COLLECT(CHILDREN([Date Column]@row), CHILDREN([Date Column]@row, @cell < [Date Column]@row)) <> "", "Expired")

KTech · 06/05/19

Thanks Paul,

When I enter this formula in the FlagTest column the cell shows #UNPARSEABLE.

It does this in both parent and child rows, in different columns as well with different tasks and dates.

The parent task should show expired if any of the sub or sub sub tasks are expired, meaning if the Due Date on the subtask is earlier than the current date.

Not sure if this is possible.

Thanks for you time.

Screen Shot 2019-06-05 at 11.24.34 AM.png

Debbie Sawyer · 06/06/19

Hi - I hope you don't mind me putting my four penneth in here, but this one intruiged me.

I believe there may have been a close bracket missing in Pauls example. Try this instead:

=IF(JOIN(COLLECT(CHILDREN([Date Due]@row), CHILDREN([Date Due]@row), @cell < [Date Due]@row)) <> "", "Expired")

Forgive the intrusion if this has already been resolved!

Kind regards

Debbie

Paul Newcome · 06/07/19

Good catch, Debbie. Thanks!!

KTech · 06/07/19

Hi Debbie,

Thanks this was the case as we discovered.

Thanks for your time.

Kenny

Cell formula that will highlight a task expired if any of its sub tasks are expired

Comments

Help Article Resources

Categories