Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
RYGB Status
I've read all of the other posts about this formula, including the Blog, but i can't seem to get this formula right. Here's what I'm trying to do:
Due Date TODAY(-5) = Green
Due Date TODAY <4>0 = Yellow
Due Date TODAY+1 = Red
Date Completed = Blue
I can get the RYG balls but I can't figure out how to ADD the criteria for the Blue ball.
RYG =IF(TODAY() - Due2 > 0, "Red", IF(TODAY() - Due2 > -3, "Yellow", "Green"))
I can get the formula for JUST the blue ball: =IF([Date Completed]1 >= TODAY(), "Blue") but I can't figure out how to incorporate this into the RYG formula. What operator should I be using? What am I doing wrong? Any help is greatly appreciated.
Comments
-
Hi Jennifer
Give this a try.
=IF(AND(NOT(ISBLANK([Date Completed]1)), [Date Completed]1 >= TODAY()), "Blue", IF(TODAY() - due1 > 0, "Red", IF(TODAY() - due1 > -3, "Yellow", "Green")))
-
HOLY SMOKES! It worked! Thanks Jason. you're a genius.
BUT, it only seems to work int he current year. It doesn't work if the dates are between two years. But I'm a lot closer.
-
Jennifer,
Can you clarify your last BUT?
Craig
-
Hello, I am not sure what I am doing wrong but I copied the same formula to my sheet and it gives me "UNPARSEABLE" error.
=IF(AND(NOT(ISBLANK([finish]22)),[finish]22>=TODAY()),"Blue", IF(TODAY() - due 1>0, "Red", IF(TODAY() - due1 > - 3, "Yellow", "Green")))
-
check your "due 1" cell reference.
Craig
-
Sorry, but how do you mean?
-
I get it now..sorry. What if I only want to use the Start and End date as a trigger for the BRYG balls. How do I modify my formula?
-
Each cell reference should be ColumnRow. If the column has spaces or special characters, use [] around the column name
[Start Date]23
for row 23, for example.
See this article on functions.
https://help.smartsheet.com/functions
You'll like want to use the following:
IF
and maybe
AND
OR
NOT
ISBLANK
but
IF
should get you started.
This formula from a previous answer above
=IF(AND(NOT(ISBLANK([Date Completed]1)), [Date Completed]1 >= TODAY()), "Blue", IF(TODAY() - due1 > 0, "Red", IF(TODAY() - due1 > -3, "Yellow", "Green")))
says
if the date completed is not blank and is more than today, then the color of the ball is blue
otherwise if today is greater than the due date (ie due date is in the past), then the color is red
if neither of those are true, then if the due date is less than 3 days in the future, then the color is yellow
and if all those are not true, the color is green.
Swap your cell references for the ones in the formula.
Craig
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