How to I change the Schedule Heath formula on the Project Plan from the PMO Template Set?

Hello, I am using the PMO Template Set from Smartsheet and am having trouble figuring out how to write a formula to change the Schedule Heath column to capture the following:

If the Status is flipped to "Complete" =Green.

If today is 5 days or fewer from the start date and until the end date OR the Status is flipped to "Complete" = Yellow.

If today is the day after the end and until status is flipped to "Complete" = Red.

If tried to use a few of the other functions that appear to be similar to my situation on this forum but....still can't get it to work.

Help!

Find more posts tagged with

Accepted answers

Paul Newcome

Try this:

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

All comments

Paul Newcome

What if the status is not complete but the end date is more than 5 days in the future?

WDE

@Paul Newcome great question. I hadn't thought about that but probably green.

Paul Newcome

Try this:

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

WDE

THANK YOU! That worked.

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.