Help Needed with Stoplight Function for Two Rows
Hello,
I have a need to create a stoplight (Red, Yellow, Green, Blue) for a project. The project needs to reference a "due date" column and a status column such that:
IF([Due Date]1 < TODAY(0), "Red", IF(Status1 = "in process", "Green", IF(Status1 = "Closed")))
Also
IF([Due Date]1 < TODAY(60), "Yellow", IF(Status1 = "In Process", "Blue"))
Then I get lost.... What my logic is in my head:
If the due date is beyond today and the status is "in process" or "not started" I need it to show red
Regardless of the due date, if a process is "closed" I need it to show green. I would also like it to show green if "inprocess" 120 to 61 days prior to the due date
I need the color to show yellow if "in process" or "not started" 60 days prior to the due date
Lastly I need blue color on all "NA" and blue on "not started" over 120 days prior to due date.
I am brand new to smart sheet, I'm trying to get the hang of this and some pieces of formula work, then when I try to add to it, everything blows up. I need everything in one formula. Can anyone point me in the right direction?
Thanks in advance,
Zach
Best Answers

I am having some difficulty following the logic you have listed. Let's walk through it stepbystep and see what we can come up with.
 Status = NA (Always Blue)
 Status = closed (Always Green)
 Status = in process
 Past due = Red
 Due date is between today and 60 days in the future = Yellow
 Due date is between 61 and 120 days in the future = Green
 Due date is greater than 120 days in the future = ?
 Status = not started
 Past due = Red
 Due date is between today and 60 days in the future = Yellow
 Due date is between 61 and 120 days in the future = ?
 Due date is greater than 120 days in the future = Blue

Thank you! That worked.
Let me just try to understand the logic of the formula piece by piece if you don't mind.
If status is "closed" then green.
If status is "NA" then Blue.
If status "in process" then green if due date is greater than or equal to 60 days from today
If status "in process" then yellow if due date is greater than today, if not then red?
Does the formula read the entire string into the logic? So it knows if status "in process" and the date isn't >=60 days in the future then it has two choices and if it's also not greater than today then it must be red? Is that why you end parenthesis there, to make the entire string a logic?
If status "not started" blue if due date is greater 120 days from today
If status "not started" Yellow if due date is greater than today but less than 120, if these two statements are incorrect then it must be red.
Is this a correct interpretation?
Answers

I am having some difficulty following the logic you have listed. Let's walk through it stepbystep and see what we can come up with.
 Status = NA (Always Blue)
 Status = closed (Always Green)
 Status = in process
 Past due = Red
 Due date is between today and 60 days in the future = Yellow
 Due date is between 61 and 120 days in the future = Green
 Due date is greater than 120 days in the future = ?
 Status = not started
 Past due = Red
 Due date is between today and 60 days in the future = Yellow
 Due date is between 61 and 120 days in the future = ?
 Due date is greater than 120 days in the future = Blue

Thank you for your support!
 Status = NA (Always Blue)
 Status = closed (Always Green)
 Status = in process
 Past due = Red
 Due date is between today and 60 days in the future = Yellow
 Due date is between 61 and 120 days in the future = Green
 Due date is greater than 120 days in the future = Green
 Status = not started
 Past due = Red
 Due date is between today and 60 days in the future = Yellow
 Due date is between 61 and 120 days in the future = Yellow
 Due date is greater than 120 days in the future = Blue
I've cleared up the two issues above.
Zach

Give this a try. It seems like it take everything into account.
IF(Status@row = "closed", "Green", IF(Status@row = "NA", "Blue", IF(Status@row = "in process", IF([Due Date]@row >= TODAY(60), "Green", IF([Due Date]@row > TODAY(), "Yellow", "Red")), IF(Status@row = "not started", IF([Due Date]@row > TODAY(120), "Blue", IF([Due Date]@row > TODAY(), "Yellow", "Red"))))))

Thank you! That worked.
Let me just try to understand the logic of the formula piece by piece if you don't mind.
If status is "closed" then green.
If status is "NA" then Blue.
If status "in process" then green if due date is greater than or equal to 60 days from today
If status "in process" then yellow if due date is greater than today, if not then red?
Does the formula read the entire string into the logic? So it knows if status "in process" and the date isn't >=60 days in the future then it has two choices and if it's also not greater than today then it must be red? Is that why you end parenthesis there, to make the entire string a logic?
If status "not started" blue if due date is greater 120 days from today
If status "not started" Yellow if due date is greater than today but less than 120, if these two statements are incorrect then it must be red.
Is this a correct interpretation?

Sure thing! Nesting IF statement such as this can get a little complicated if there are several conditions involved.
If status is "closed" then green.
If status is "NA" then Blue.
If status "in process" then green if due date is greater than or equal to 60 days from today
If status "in process" then yellow if due date is greater than today, if not then red?
This is exactly right!
Does the formula read the entire string into the logic? So it knows if status "in process" and the date isn't >=60 days in the future then it has two choices and if it's also not greater than today then it must be red? Is that why you end parenthesis there, to make the entire string a logic?
Sort of... I guess?
The basic syntax for an IF statement is IF(logical_expression, value_if_true, [value_if_false]). When we start testing for multiple conditions, we "nest" an additional IF statement inside the original statement. The typical way to do this is to add the "inner" IF as the [value if false]. You can however also use a nested if as the [value if true]. For this section, we are sending the logic down different paths of additional IF statements depending on whether the status is "in process". This logic essentially repeats when we evaluate for "not started".
It helps to understand that IF statements are only evaluated until one of the conditions is met. To use some pseudocode:
IF(sky = blue, cheer, IF(sky = red, run, IF(sky = black, sleep, "")))
So, we cheer if the sky is blue, if not... we check to see if it is red, if so, we run. If not, we check to see if it is black. If it is, we sleep, if not, we do nothing "". If the sky is indeed blue, then we will cheer, but at that point nothing else is evaluated. There are other conditions in the statement, but they will not be parsed once condition has been met and its relevant action performed. This is why it is good practice to place conditions that are more likely to be met earlier in a statement when it is possible. This can save on compute power, especially when you have a large sheet and are dealing with column formulas.
For reference, here is this formula broken out... this helps me visualize the logic.
I hope this helps at least a little!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.8K Get Help
 65 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 56 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!