RYG Status Formula Based on % Complete and Start Date

Hi All,

I'm a novice user of Smartsheet. I'm trying to add a formula based on % Complete and the Start Date. I'd like to automate the status based on the following parameters:

If % Complete is greater than .24 = Green

If % Complete is greater than 0.14 = Yellow

If % Complete is less than 0.14 = Red

If Start Date is in the future = blank

Any suggestions?

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

Try this:

=IF([Start Date]@row<= TODAY(), IF([% Complete]@row< 0.14, "Red", IF([% Complete]@row< 0.24, "Yellow", "Green")))

Lbrady

Hi Paul,

Thanks for the quick response. It didn't quite work.

It did make the future dates blank, but the tasks completed also turned red when converted to a column formula.

Do you have any recommendations?

Paul Newcome

How exactly is the % Complete column being populated? Is it a text/number column formatted to show percentages when you type in a number, or do you have to manually type in the % every time?

Lbrady

Hi Paul,

I tried the formula again, and it worked this time!

If I wanted to make changes to the parameters (what's considered complete, etc.), could you help me with that formula?

If % Complete is 100% = Green

If % Complete is greater than 0= Yellow

If % Complete is 0 = Red

If Start Date is in the future = blank

Thanks for all 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.