Formula needs to ignore parents

Hi All

I have a formula that puts a RAG status depending on when the task is due:

=IF([% Complete]@row = 1, "Blue", IF(Finish@row > TODAY(+7), "Green", IF(AND(Finish@row >= TODAY(+3), Finish@row < TODAY(+7)), "Yellow", IF(Finish@row < TODAY(+3), "Red", "Nope"))))

It works fine but copied down it applies the formula to every row. I would like it to only apply the formula against actual tasks/children, and not against any parent or summary tasks. I do appreciate that it is useful to see that the parent summary also has red tasks in it, but the purpose of this for me is to

a) count the tasks I have that are R,Y,G and count only the tasks. I therefore want to exclude parents.

Should I be trying to do with more with a countif (i.e. count if red and it not a parent?)

This enables me to report that I have 50 tasks, 10 are due this week, ten are due next week and 10 beyond that (just an example, I know my formula is 3-6. +7 etc!!)

I hope this makes sense.

Thank you!!

Find more posts tagged with

Accepted answers

Heather Duff

Hello!

You could try doing something like this:

=if(count(children())>0,"",IF([% Complete]@row = 1, "Blue", IF(Finish@row > TODAY(+7), "Green", IF(AND(Finish@row >= TODAY(+3), Finish@row < TODAY(+7)), "Yellow", IF(Finish@row < TODAY(+3), "Red", "Nope")))))

This should make the field display as blank if there are any children rows. However, if any of your "children" rows have additional subtasks, this cell would be blank for them, too.

Hope this helps!

Best,

Heather

All comments

Heather Duff

Hello!

You could try doing something like this:

This should make the field display as blank if there are any children rows. However, if any of your "children" rows have additional subtasks, this cell would be blank for them, too.

Hope this helps!

Best,

Heather

JoG

That's amazing, worked perfectly Thank you Heather!

Heather Duff

Fantastic! Glad it worked. Have a great weekend!

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.