Helper Formula for filtering contradicting Status

I can't quite get the filter to align with my intent. I'm either pulling every status when I want to exclude "Complete" tasks or I can't get parent rows for my project schedule. I want the attached report to do the following for ease of presentation past due AND upcoming activities.

Status is one of Not Started or In Progress

Finish date is in the past (not started or in progress)

Start date is 21 days in the future (not started or in progress)

Include Parent Rows where child task fits the criteria above. Always include row 1.

SBE - Pelican's Jaw (PV + BESS).xlsx

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

Give this a try:

=IF(OR(Row@row = 1, COUNTIFS(CHILDREN(), @cell = 1) > 0, AND(COUNT(CHILDREN(Task@row)) = 0, Status@row <> "Complete", OR([Finish Date]@row < TODAY(), [Start Date]@row < TODAY(21)))), 1)

JSexton

I may have figured it out, this seems to have worked. Thanks again for your support here, very much appreciated Paul.

=IF(OR(Row@row = 1, COUNTIFS(CHILDREN(), @cell = 1) > 0, AND(COUNT(CHILDREN(Task@row)) = 0, NOT(ISBLANK(Status@row)), NOT(ISBLANK([Finish Date]@row)), Status@row <> "Complete", OR([Finish Date]@row < TODAY(), [Start Date]@row < TODAY(21)))), 1)

All comments

Paul Newcome

First insert an auto-number type column. Formatting doesn't matter here, but for this example, I will call it "Auto".

Next is a text/number column called "Row" with the following column formula:

=MATCH(Auto@row, Auto:Auto, 0)

Next is a checkbox type column. This will have the below column formula that checks the box on every row that needs to be included:

=IF(OR(Row@row = 1, COUNTIFS(CHILDREN(), @cell = 1) > 0, AND(Status@row <> "Complete", OR([Finish Date]@row < TODAY(), [Start Date]@row < TODAY(21)))), 1)

The above makes an assumption that for [Start Date] you wanted rows that are within the next 21 days as opposed to exactly 21 days in the future.

The AND portion says if the status is not "Complete" and either the Finish Date is in the past or the Start Date is in the next 21 days.

The outer OR says if the above AND is true, or the row number equals 1, or there is at least one checked child row.

JSexton

Hi Paul! Thank you for putting this together. This worked as expected. My only other bit of refinement, if possible, would be to remove parent rows if there are no child tasks that fall into the past due or 21 days in the future criteria. Thus only showing the pertinent details.

As an example, the area in red I'd like to not be part of the filter since the underlying tasks to these parent rows are not past due or scheduled to start in the next 21 days. Is it possible to align the formula in the checkbox column to take this into account?

Paul Newcome

That red box consists of only parent rows. Are there any child rows within that are checked if you expand those parent rows?

JSexton

All of the child tasks under those respective parent rows are "complete"

Paul Newcome

Can you provide a screenshot?

JSexton

Here's a section of our design and the subsequent tasks;

Paul Newcome

Give this a try:

=IF(OR(Row@row = 1, COUNTIFS(CHILDREN(), @cell = 1) > 0, AND(COUNT(CHILDREN(Task@row)) = 0, Status@row <> "Complete", OR([Finish Date]@row < TODAY(), [Start Date]@row < TODAY(21)))), 1)

JSexton

Paul, you're a godsend here! I have a tad bit more I'd like to narrow down as the last formula certainly worked as expected but I have the two scenarios below I'd like to adjust. In the examples below, if the start date and finish date are blank and/or the Child status is blank, I'd like to remove these as well to pare down my filter.

Child task without a date to remove parent row and child tasks - see red highlights.

Blank Child Tasks without a status and/or dates - this may be populating because there's a note in a separate column;

JSexton

I may have figured it out, this seems to have worked. Thanks again for your support here, very much appreciated Paul.

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.