Hello! I'm trying to write a formula that will update the Health symbol of a task based on some of the other columns in my Smartsheet. I think I'm close, but I don't quite have it. I want to set the Health symbol to "Yellow" if the date is within 7 days prior to the Target Completion Date OR if my % Complete column is less than the % in the YTD Time Elapsed column by 2% or less. I was able to come up with working formulas for each of these things separately, but I'm having trouble putting them together into one formula. These are the 2 pieces of formula I came up with: =IF([Target Completion Date]@row > TODAY() - 7, "Yellow") =IF(AND([% Complete]@row > ([YTD Time Elapsed]@row - 0.02), [% Complete]@row ([YTD Time Elapsed]@row - 0.02), [% Complete]@row TODAY() - 7), "Yellow")) Note that I plan on putting this into a complete formula where the Health symbol is * Gray if the Status column is Not Started * =IF((Status@row = "Not Started"), "Gray") * Red if the Target Completion Date has passed OR % Complete is more than 2% less than the % in the YTD Time Elapsed column* =IF(OR([Target Completion Date]@row < TODAY(), [% Complete]@row < ([YTD Time Elapsed]@row - 0.02)), "Red") * Green if none of the situations for Gray, Red, or Yellow are met (on or after the Target Start Date but at least 7 days - or 5 workdays - prior to the Target Completion Date AND % Complete equals or is greater than the % in the YTD Time Elapsed column) Sorry for the long post, but I'd appreciate any help anyone can provide. Thanks!

What's wrong with my formula for setting the Health of a task?

Hello!

I'm trying to write a formula that will update the Health symbol of a task based on some of the other columns in my Smartsheet. I think I'm close, but I don't quite have it.

I want to set the Health symbol to "Yellow" if the date is within 7 days prior to the Target Completion Date OR if my % Complete column is less than the % in the YTD Time Elapsed column by 2% or less. I was able to come up with working formulas for each of these things separately, but I'm having trouble putting them together into one formula. These are the 2 pieces of formula I came up with:

=IF([Target Completion Date]@row > TODAY() - 7, "Yellow")

=IF(AND([% Complete]@row > ([YTD Time Elapsed]@row - 0.02), [% Complete]@row < [YTD Time Elapsed]@row), "Yellow")

The combined formula I came up with is below. It returns the Yellow symbol correctly for the % Complete part of the formula but not the date part of the formula.

=IF(AND([% Complete]@row > ([YTD Time Elapsed]@row - 0.02), [% Complete]@row < [YTD Time Elapsed]@row), IF(OR([Target Completion Date]@row > TODAY() - 7), "Yellow"))

Note that I plan on putting this into a complete formula where the Health symbol is

Gray if the Status column is Not Started
- =IF((Status@row = "Not Started"), "Gray")
Red if the Target Completion Date has passed OR % Complete is more than 2% less than the % in the YTD Time Elapsed column
- =IF(OR([Target Completion Date]@row < TODAY(), [% Complete]@row < ([YTD Time Elapsed]@row - 0.02)), "Red")
Green if none of the situations for Gray, Red, or Yellow are met (on or after the Target Start Date but at least 7 days - or 5 workdays - prior to the Target Completion Date AND % Complete equals or is greater than the % in the YTD Time Elapsed column)

Sorry for the long post, but I'd appreciate any help anyone can provide.

Thanks!

Find more posts tagged with

Accepted answers

All comments

Deric

=if(status@row = "Not Started", "Gray", if(or([Target Completion Date]@row < Today(), [% Complete]@row < (YTD Time Elapsed]@row - 0.02)), "Red", if(or(and([% Complete]@row > [YTD Time Elapsed]@row - 0.02, [% Complete]@row < [YTD Time Elapsed]@row), [Target Completion Date]@row > TODAY() - 7), "Yellow", "Green")))

I didn't test it, but it should be close. The main problem with your "Yellow" formula is that you were saying "test if A and B are both true. Then if A and B are both true, test if C is true. Then if C is true, print "Yellow". So if not C, then not "yellow". When what you needed to do was "test if A and B are both true AND test if C is true. IF A and B are both true OR if C is true, then print "Yellow".

As an aside, it seems like you are comparing percentages and days. If this is the case, there is a good chance that you are not accounting for all permutations and you will get rouge results that will require a more complex formula to account for them all. If this is the case, you might be better served by created a matrix and using index and match to get the status.

scisar

Thanks Daric,

That didn't quite work - anything where % Complete was higher than YTD Time Elapsed was also returning Yellow. But, I managed to work around it by putting Green before Yellow in the formula.

I appreciate your 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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.