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

KTech

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

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

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.

Debbie Sawyer

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

Good catch, Debbie. Thanks!! 

KTech

Hi Debbie,

Thanks this was the case as we discovered.

Thanks for your time.

Kenny