Updating threat level based on percent complete

Hi,

Could someone help guide me with what is wrong with my formula?

What I am looking for the formula to output:

If the Percent complete is less than 30% within 30 days of the Current milestone date, Threat level is a yellow symbol. If the Percent complete does not equal 30%, today is after Current milestone date, the Threat level is a red symbol.

Current attempt:

=IF(AND([Percent complete]@row < 30, TODAY() <= ([Current milestone date]@row + 30)),"Yellow", IF(AND([Percent complete]@row <> 30, TODAY() >[Current milestone date]@row), "Red", ""))

Kind regards

Find more posts tagged with

Accepted answers

heyjay

=IF(AND([Percent complete]@row <> 0.3, TODAY() > [Current milestone date]@row), "Red", 
IF(AND([Percent complete]@row < 0.3, TODAY() <= ([Current milestone date]@row + 30)), "Yellow", 
"Green"))

Same formula as the previous but making use of the "'otherwise' make it green" at the end.

All comments

heyjay

=IF(AND([Percent complete]@row <> 0.3, TODAY() > [Current milestone date]@row), "Red", 
IF(AND([Percent complete]@row < 0.3, TODAY() <= ([Current milestone date]@row + 30)), "Yellow", ""))

Please use decimal number in your formula when comparing to percentages in cells. Swap the RED and YELLOW formula.

MD_Accel 23

Thank you so much! It worked, kindly appreciate your help @heyjay !

Is there a way to set the threat level to be a green status? I tried to set this up with an automation but symbols aren't an option for a cell value change in automation.

heyjay

Using a formula? Yes, you just need to add another criteria for green, replace the items in bold fontface.

=IF(AND([Percent complete]@row <> 0.3, TODAY() > [Current milestone date]@row), "Red", 
IF(AND([Percent complete]@row < 0.3, TODAY() <= ([Current milestone date]@row + 30)), "Yellow", 
IF(AND([Percent complete]@row < 0.3, TODAY() <= ([Current milestone date]@row + 30)), "Green",
""))

MD_Accel 23

My apologies, if using a formula I would look to have it be at green status if it is not at a yellow or red status.

MD_Accel 23

I tried something like so…

=IF([Threat Level]@row <>"Red","Yellow",TODAY()<=([Currentmilestone date]@row +30)), "Green", IF(AND([Percentcomplete]@row <>0.3, TODAY() > [Current milestone date]@row), "Red", IF(AND([Percentcomplete]@row <0.3, TODAY() <= ([Currentmilestone date]@row +30)), "Yellow", ""))

heyjay

=IF(AND([Percent complete]@row <> 0.3, TODAY() > [Current milestone date]@row), "Red", 
IF(AND([Percent complete]@row < 0.3, TODAY() <= ([Current milestone date]@row + 30)), "Yellow", 
"Green"))

Same formula as the previous but making use of the "'otherwise' make it green" at the end.

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.