Function for RYGB Ball

Options
Shannon Heward
Shannon Heward ✭✭✭✭
edited 06/15/20 in Formulas and Functions

I'm trying to create a formula for the ball to automatically update based on a "Status" column and a "Modified Date" column. Here's what I have so far...part of it appears to be working, but I'm having difficulty with the Red and Yellow/Green part of the function. Can someone please help?


=IF(Status@row = "Executed", "Blue", IF(Status@row = "Canceled", "Blue", IF(AND(Status@row = "Waiting for Requestor Response", (([Modified Date]@row) < TODAY(-60))), "Red", IF(AND(Status@row = "WD Legal Review", (([Modified Date]@row) < TODAY(-60))), "Red", IF(AND(Status@row = "Customer Legal Review", (([Modified Date]@row) < TODAY(-60))), "Red", IF(AND(Status@row = "Sent for Signatures", (([Modified Date]@row) < TODAY(-60))), "Red", IF(AND(Status@row = "Waiting for Requestor Response", (([Modified Date]@row) < TODAY(-30))), "Yellow", "Green", IF(AND(Status@row = "WD Legal Review", (([Modified Date]@row) < TODAY(-30))), "Yellow", "Green", IF(AND(Status@row = "Customer Legal Review", (([Modified Date]@row) < TODAY(-30))), "Yellow", "Green", IF(AND(Status@row = "Sent for Signatures", (([Modified Date]@row) < TODAY(-30))), "Yellow", "Green"

Thank you!

Best Answers

  • Shannon Heward
    Shannon Heward ✭✭✭✭
    Answer ✓
    Options

    Thank you so much!! This is perfect and I greatly appreciate your help! There are no other Status types...I was making it too complicated!

Answers

  • Shannon Heward
    Shannon Heward ✭✭✭✭
    Answer ✓
    Options

    Thank you so much!! This is perfect and I greatly appreciate your help! There are no other Status types...I was making it too complicated!

  • Genevieve P.
    Options

    Hi Shannon,

    No worries at all! Very happy to help, and I'm glad it worked for you. 🙂

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!