Function for RYGB Ball
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
-
Hi Shannon,
It seems like you may just have a few extra parentheses in there, which is causing sections to close off before you're ready to tell the formula to stop.
We can simplify this formula by using the OR function. I'll break it out by colour, first, then provide the full thing:
BLUE:
=IF(OR(Status@row = "Executed", Status@row = "Canceled"), "Blue",
RED:
IF(AND(OR(Status@row = "Waiting for Requestor Response", Status@row = "WD Legal Review", Status@row = "Customer Legal Review", Status@row = "Sent for Signatures"), [Modified Date]@row < TODAY(-60)), "Red",
YELLOW:
IF(AND(OR(Status@row = "Waiting for Requestor Response", Status@row = "WD Legal Review", Status@row = "Customer Legal Review", Status@row = "Sent for Signatures"), [Modified Date]@row < TODAY(-30)), "Yellow", "Green")))
FULL FORMULA:
=IF(OR(Status@row = "Executed", Status@row = "Canceled"), "Blue", IF(AND(OR(Status@row = "Waiting for Requestor Response", Status@row = "WD Legal Review", Status@row = "Customer Legal Review", Status@row = "Sent for Signatures"), [Modified Date]@row < TODAY(-60)), "Red", IF(AND(OR(Status@row = "Waiting for Requestor Response", Status@row = "WD Legal Review", Status@row = "Customer Legal Review", Status@row = "Sent for Signatures"), [Modified Date]@row < TODAY(-30)), "Yellow", "Green")))
ALTERNATE OPTION:
Can I also ask, are these the only types of statuses that you'll have?
Executed / Canceled / Waiting for Requestor Response / WD Legal Review / Customer Legal Review /Sent for Signatures
If so, we can simplify this even further, because you state at the beginning what to do for "Executed" and "Canceled"... therefore the formula will only continue to the next colour if the status is not one of those two. This means you don't have to list them at all and can just focus on the dates!
Try this:
=IF(OR(Status@row = "Executed", Status@row = "Canceled"), "Blue", IF([Modified Date]@row < TODAY(-60), "Red", IF([Modified Date]@row < TODAY(-30), "Yellow", "Green")))
Let me know if you have any questions about this!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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
-
Hi Shannon,
It seems like you may just have a few extra parentheses in there, which is causing sections to close off before you're ready to tell the formula to stop.
We can simplify this formula by using the OR function. I'll break it out by colour, first, then provide the full thing:
BLUE:
=IF(OR(Status@row = "Executed", Status@row = "Canceled"), "Blue",
RED:
IF(AND(OR(Status@row = "Waiting for Requestor Response", Status@row = "WD Legal Review", Status@row = "Customer Legal Review", Status@row = "Sent for Signatures"), [Modified Date]@row < TODAY(-60)), "Red",
YELLOW:
IF(AND(OR(Status@row = "Waiting for Requestor Response", Status@row = "WD Legal Review", Status@row = "Customer Legal Review", Status@row = "Sent for Signatures"), [Modified Date]@row < TODAY(-30)), "Yellow", "Green")))
FULL FORMULA:
=IF(OR(Status@row = "Executed", Status@row = "Canceled"), "Blue", IF(AND(OR(Status@row = "Waiting for Requestor Response", Status@row = "WD Legal Review", Status@row = "Customer Legal Review", Status@row = "Sent for Signatures"), [Modified Date]@row < TODAY(-60)), "Red", IF(AND(OR(Status@row = "Waiting for Requestor Response", Status@row = "WD Legal Review", Status@row = "Customer Legal Review", Status@row = "Sent for Signatures"), [Modified Date]@row < TODAY(-30)), "Yellow", "Green")))
ALTERNATE OPTION:
Can I also ask, are these the only types of statuses that you'll have?
Executed / Canceled / Waiting for Requestor Response / WD Legal Review / Customer Legal Review /Sent for Signatures
If so, we can simplify this even further, because you state at the beginning what to do for "Executed" and "Canceled"... therefore the formula will only continue to the next colour if the status is not one of those two. This means you don't have to list them at all and can just focus on the dates!
Try this:
=IF(OR(Status@row = "Executed", Status@row = "Canceled"), "Blue", IF([Modified Date]@row < TODAY(-60), "Red", IF([Modified Date]@row < TODAY(-30), "Yellow", "Green")))
Let me know if you have any questions about this!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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!
-
Hi Shannon,
No worries at all! Very happy to help, and I'm glad it worked for you. 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!