NEED HELP: Health Formula based on start date, end date, and %100 complete needed.

Hi Guys,

I'm really struggling with a smartsheet formula.

This is what I'm attempting:

=IF([Plan Start]@row > TODAY(), "Green", IF([Plan Start]@row < TODAY(), "Yellow", IF([Due Date]@row >[Due Date]@row TODAY(), "Red") OR(IF([% Complete]@row = "1", "Blue")))

Essentially I'm trying to make a formula that says: If It's before start day = blank, if start date or after = green, if 1 week before due date = yellow, if due date or after = red, if % Complete column is 100% = blue

Any help is appreciated

Find more posts tagged with

Accepted answers

All comments

Sam M.

Hi Daniel,

I did some testing and this might work:

The formula I used was:

=IF([% Complete]@row = 1, "Blue", IF([Plan Start]@row > TODAY(), "", IF(AND([Due Date]@row <= TODAY(), [Plan Start]@row < TODAY()), "Red", IF(NOT([Due Date]@row > TODAY(+7)), "Yellow", IF([Plan Start]@row <= TODAY(), "Green")))))

The formula is based on what is being asked:

if % Complete column is 100% = blue

If It's before start day = blank,

if due date or after = red,

if 1 week before due date = yellow,

if start date or after = green,

Hope it helps,

Sam

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.