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

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

Find more posts tagged with

Education

Formulas

IT & Operations

Card View

Comments

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.

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

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

Sign in to join the conversation!

Quick Links

Trending discussions

Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?