Help Needed with Stoplight Function for Two Rows

Zach2050

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 "in-process" 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

Carson Penticuff

I am having some difficulty following the logic you have listed. Let's walk through it step-by-step 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

Zach2050

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?

Carson Penticuff

I am having some difficulty following the logic you have listed. Let's walk through it step-by-step 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

Zach2050

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

Carson Penticuff

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"))))))

Zach2050

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?

Carson Penticuff

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 pseudo-code:

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!