Could anyone help us create the proper formula? Parent/Children Health based off Due Dates

We are trying to utilize Smartsheet to list out, follow, and forecast our project schedules. The idea is that each Milestone of the Project is the Parent and the steps within the milestone are the children. We are having issues with the parents correctly representing an issue/delay within its children. Right now the formula we are using is:

=IF([Start Date]39 > TODAY(), "Blue", IF(AND([% Complete]39 < 1, [End Date]39 < TODAY()), "Red", IF(COUNTIFS(CHILDREN(), "Red") > 0, "Yellow", "Green")))

this was taken from row 39 of a sheet (would love a universal @ row Formula)

Essentially we want the health to reflect the steps current status in this way

Red means Overdue (Due Date>Today )

Yellow means Status is In Progress AND Due Date upcoming (Due Date<14 Days away)

Green means Status is in Progress, Start date has passed, but has not within 14 days of being Due

Blue means Status is In Queue

If anyone can assist with this vision it would be much appreciated!

Find more posts tagged with

Templates

Formulas

Support

Conditional Formatting

Project Management

Accepted answers

All comments

Hank Dunlap

If you are using %complete, you can use something like this:

=IF([% Complete]@row = 1, "Green", IF([End Date]@row < TODAY(), "Red", IF([End Date]@row <= TODAY(3), "Yellow", "Green")))

Does that help?

Martin T.

Thank you for your suggestion Hank!

We are more looking to make a formula which reflects something like this: (for some reason this is Unparseable)

=IF([Start Date]@row > TODAY(), "blue"), IF([% Complete]@row > 1, "yellow"), IF(AND([% Complete]@row < 100, ([End Date]@row > TODAY()), "Red")

essentially combining the four formulas below into one formula

=IF([Start Date]@row > TODAY(), "blue")

=IF([% Complete]@row >= 1, "yellow")

=IF(AND([% Complete]@row < 100, ([End Date]@row > TODAY()), "Red")

=IF(Status@row = "Complete", "Green")

Paul Newcome

What if a task is less than 14 days away? You could have a task that is not yet started but the due date is within 14 days?

Based on your most recent post, I would use something along the lines of...

=IF(Status@row = "Complete", "Green", IF([Start Date]@row> TODAY(), "Blue", IF([End Date]@row< TODAY(), "Red", "Yellow")))

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.