RGYB Ball Formula
Hi there, Trying to create a formula where it uses date for red, yellow, green and then Complete checkbox for blue. Below is where I'm at, but it's not functioning. Any advice?
=IF(Finish@row - 3 <TODAY(), "Red", IF(Finish@row - 10< TODAY(), "Yellow", IF(Finish@row - 15< TODAY(), "Green", IFAND(Complete@row = 1,"Blue"))))]
Best Answer
-
Hello @Maura White ,
Based on what you've provided, it looks like you want the formula to say; If the Finish date is less than 3 days from Today, put red, If the Finish date is less than 10 days from Today, put Yellow etc.
Please see the example I was able to create that should help achieve this:
=IF(Complete@row = 1, "Blue", IF(Finish@row = TODAY(-15), "Green", IF(Finish@row = TODAY(-10), "Yellow", IF(Finish@row = TODAY(-3), "Red"))))
As you can see, if the Finish date is X amount of days before Today, it will input the corresponding color. If the Complete checkbox is checked, it places the Blue RGYB ball. Lastly, if the Finish date is X amount of days before, but the Complete checkbox is checked, it will still place the Blue RGYB ball.
If you'd like it to be days greater than today, then you can remove the "-" from within the TODAY brackets. E.G TODAY(15)
Kindest Regards
Sean
Answers
-
Hello @Maura White ,
Based on what you've provided, it looks like you want the formula to say; If the Finish date is less than 3 days from Today, put red, If the Finish date is less than 10 days from Today, put Yellow etc.
Please see the example I was able to create that should help achieve this:
=IF(Complete@row = 1, "Blue", IF(Finish@row = TODAY(-15), "Green", IF(Finish@row = TODAY(-10), "Yellow", IF(Finish@row = TODAY(-3), "Red"))))
As you can see, if the Finish date is X amount of days before Today, it will input the corresponding color. If the Complete checkbox is checked, it places the Blue RGYB ball. Lastly, if the Finish date is X amount of days before, but the Complete checkbox is checked, it will still place the Blue RGYB ball.
If you'd like it to be days greater than today, then you can remove the "-" from within the TODAY brackets. E.G TODAY(15)
Kindest Regards
Sean
-
@Sean Morgan when i attempt this formula i get an error. #UNPARSEABLE. I dont need the "completed" checkbox. What i was looking for was a formula that would essentially say "If request date is less than 15 days, green, if less than 30 days, yellow, if less than 60, red, all others are blue"
-
Hi @Toby W
What is the formula you're currently working with?
You could try something like this, depending on your end goal:
=IF([Request Date]@row <= TODAY(-60), "Red", IF([Request Date]@row <= TODAY(-30), "Yellow", IF([Request Date]@row >= TODAY(-15), "Green")))
Break out each statement to see what it says:
=IF([Request Date]@row <= TODAY(-60), "Red",
If the Date in the Request Date column is more than 60 days in the past, turn Red
IF([Request Date]@row <= TODAY(-30), "Yellow",
If the Date in the Request Date column is more than 30 days in the past, but less than 60 days in the past, turn Yellow
IF([Request Date]@row >= TODAY(-15), "Green", "Blue")))
If the Date in the Request Date column is between 15 days in the past, today, or in the future, return Green.
Although there's a blue statement, we won't get to that statement as all dates are covered. If this isn't what you want to do, please write out exactly what you want for each status, with a screen capture example if possible.
Cheers,
Genevieve
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
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!