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

Lisa Matthews
Lisa Matthews ✭✭✭✭✭
edited 03/11/20 in Formulas and Functions

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.

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    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

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    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

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • Lisa Matthews
    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

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

    -Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!