How to check Finish date and %Complete to determine RYG symbol?

Hi Expert,

Seeking advice how can I come out with a multi-condition formula from 2 columns with below criteria? Say I have a column name %Complete and another column - Finish Date

If %Complete not 100% and Finish Date is smaller than today(), show Red symbol

If Finish Date is larger than today(), show Green symbol

If Today () - Finish Date more than 3 days, show Yellow symbol

If %Complete = 100%, show Gray symbol

Many thanks.

Find more posts tagged with

Formulas

Accepted answers

David Tutwiler

It's all about Nested Ifs. I may have misinterpreted your conditions, but this formula does the following:

Grey if % complete is 100%

Yellow if Today - Finish Date is within 3 days

Red if Complete is not 100% and the Finish Date is past today's date

Green if the Complete is not 100% and Finish Date is before today's date

=IF([%Complete]@row = 1, "Gray", IF([Finish Date]@row > TODAY(), "Red", IF(TODAY() - [Finish Date]@row <= 3, "Yellow", "Green")))

All comments

David Tutwiler

It's all about Nested Ifs. I may have misinterpreted your conditions, but this formula does the following:

Grey if % complete is 100%

Yellow if Today - Finish Date is within 3 days

Red if Complete is not 100% and the Finish Date is past today's date

Green if the Complete is not 100% and Finish Date is before today's date

=IF([%Complete]@row = 1, "Gray", IF([Finish Date]@row > TODAY(), "Red", IF(TODAY() - [Finish Date]@row <= 3, "Yellow", "Green")))

Angeline Lai

Thanks David! It is working. Appreciated it. 😀

Angeline Lai

https://community.smartsheet.com/discussion/comment/279163#Comment_279163

Apologies. Only Gray and Red working but not Yellow/ Green. Wonder why? I tried editing the days but for criterai today - minus <=-3., either return yellow or all become green for all. No differentiator even in task <= -3 versus task more than 3 days ...Pls. advise where did I go wrong? pls. refer my snapshot. Many Thanks

Angeline Lai

Finish 24 Feb - show Yellow

Finish 8 Mar - should show Green but showing Yellow 🙄

David Tutwiler

Try this:

=IF([%Complete]@row = 1, "Gray", IF([Finish Date]@row > TODAY(), "Red", IF([Finish Date]@row - TODAY() <= 3, "Yellow", "Green")))

Angeline Lai

Hi David,

Seem no luck :( Now everything turn Red ...hmmm.. is there anyway to test and debug the function? Thanks

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.