Risk Formula (Red, Yellow, Green)

I am using the formula below to automatically calculate risk. I am using percent complete of a task against time complete off the start and end dates.

=IF([%CMPLT]@row >= (NETDAYS(TODAY(), [End Date]@row) / NETDAYS([Start Date]@row, [End Date]@row)), "Green", IF([%CMPLT]@row > 0, "Yellow", "Red"))

So if my start and end date calculates 50% of the time complete and we only have 30% of that task done we will get a "Yellow" orb.

This formula works great if we have current start dates. But if the start date is in the future the orb turns red. I don't know how to make a condition saying if start date is in the future default the orb to "green."

Any help would be much appreciated.

Find more posts tagged with

Formulas

Accepted answers

ChristineB

@Colleen Patterson

YES!

=IF([Start Date]@row > TODAY(), "Green", IF([%CMPLT]@row >= (NETDAYS(TODAY(), [End Date]@row) / NETDAYS([Start Date]@row, [End Date]@row)), "Green", IF([%CMPLT]@row > 0, "Yellow", "Red"))

This worked, thank you so much!

All comments

Colleen Patterson

@ChristineB

=IF([Start Date]@row > TODAY(), "Green", IF[%CMPLT]@row >= (NETDAYS(TODAY(), [End Date]@row) / NETDAYS([Start Date]@row, [End Date]@row)), "Green", IF([%CMPLT]@row > 0, "Yellow", "Red"))

This should fix the problem, as it will first look for any that are starting in the future and make those green, before looking at any of the other values.

ChristineB

@Colleen Patterson

YES!

=IF([Start Date]@row > TODAY(), "Green", IF([%CMPLT]@row >= (NETDAYS(TODAY(), [End Date]@row) / NETDAYS([Start Date]@row, [End Date]@row)), "Green", IF([%CMPLT]@row > 0, "Yellow", "Red"))

This worked, thank you so much!

Colleen Patterson

Glad I was able to help. @ChristineB

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.