Multiple ifs when looking at child tasks

Hi All

I've got this formula which checks if a line has children then if any of them are "RAG" Green it prints a 1. I am using it to create a report with nested line items

=IF(COUNT(CHILDREN([Task Name]@row)) > 0, IF(COUNTIFS(DESCENDANTS(RAG@row), "Green") > 0, 1))

I am trying to limit returning "1" only if any of the children have a "Finish" date less than 7 days in the future by adding to this something like

=IF([Finish]@row < TODAY(+7))

but im struggling to get these 2 to work together with the correct syntax.

Any help is appreciated.

Find more posts tagged with

Accepted answers

Karve

Thanks for the direction Paul

=IF(COUNT(CHILDREN([Task Name]@row)) > 0, IF(COUNTIFS(DESCENDANTS(RAG@row), "Green", DESCENDANTS(Finish@row), <TODAY(+7)) > 0, 1))

Gave me what I needed

All comments

Paul Newcome

You would add another range/criteria set looking at the DESCENDANTS of your date column with the criteria you have listed below.

Karve

Thanks for the direction Paul

=IF(COUNT(CHILDREN([Task Name]@row)) > 0, IF(COUNTIFS(DESCENDANTS(RAG@row), "Green", DESCENDANTS(Finish@row), <TODAY(+7)) > 0, 1))

Gave me what I needed

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.