RYG Formatting - Formula for Populating Status using %Complete and Finish Date

HELP!!! I am trying to populate my Status field, using RYG formatting (using the % Complete and Finish Date). The cell displays #UNPARSEABLE. I am not sure what I am doing wrong. I have been reading through the comments in the community and just cannot get this right.

I used this formula in Excel and it worked, but when I copy it into Smartsheet, it does not work at all:

=IFS(AND(I7<TODAY(),D7<1),"Red",AND(I7=TODAY(),D7<1),"Yellow",AND(I7>TODAY(),D7<1),"Green",D7=1,"Gray")

My Status Definitions:

Gray - Finished

Yellow – At Risk

Green – On Track

Red - Late

I would like to get the formula to calculate:

· If % Complete = 1 then Status is GRAY

· If Finish Date is less than Today AND % Complete is less than 1, then Status is RED

· If Finish Date = Today + the next 5 days and % Complete is less than 1, then Status is YELLOW (couldn’t figure out how to add the 5 days)

· If Finish Date is greater than Today + 5 days and % Complete is less than 1, then Status is GREEN (couldn’t figure out how to exclude the “yellow” days from this formula)

These are the two versions of the formula that I have tried. Neither one works:

=IF(AND([% Complete]7 <

1, Finish7 < TODAY()), "Red", IF(AND([% Complete]7 <

1, Finish7 < TODAY()), "Yellow", IF(AND([% Complete]7 <

1, Finish7 > TODAY()), "Green", IF([% Complete]7 >

1, "Gray,))))

=IF(AND(Finish7 < TODAY()), ([% Complete]7<1), "Red"), (AND(Finish7 = TODAY()), ([% Complete]7<1), "Yellow"), (AND (Finish7 > TODAY()), ISBLANK([% Complete]7), "Green"), IF(([% Complete]7 = 1), "Gray")

Thank you in advance for helping me. I cannot figure this out by myself.

Find more posts tagged with

Accepted answers

Genevieve P.

Hi Lisa,

Since you want the status to be Gray if it's 100%, you can put this rule first in line, and then you don't need to specify the "less than 100%" for all the other criteria because the formula will only move to those if it's less than 100%.

Try this:

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

I didn't specify the Green rule because it will apply if none of the other criteria are met... namely, if the % is not 100% and the date is more than 5 days in the future.

Here are some articles that may help you:

Let me know if this works for you or if you have any questions!

Cheers,

Genevieve

All comments

Genevieve P.

Hi Lisa,

Try this:

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

I didn't specify the Green rule because it will apply if none of the other criteria are met... namely, if the % is not 100% and the date is more than 5 days in the future.

Here are some articles that may help you:

Let me know if this works for you or if you have any questions!

Cheers,

Genevieve

Lisa Matthews

Hi Genevieve,

Thank you so much for the explanation and formula!!! I have a better understanding of the logic and the formula you provided works exactly the way I wanted!

A million thanks!

--Lisa

Genevieve P.

Wonderful, I'm so glad to hear that! And happy to help.

-Genevieve

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.