Time to break out those formula skills again! This week, we’ve got another slick “nested IF” formula for you to use to automate Red-Yellow-Green symbols. Commonly referred to as “those handy RYG balls,” these symbols are often used to indicate if a task is on time (Green), approaching its due date (Yellow), or overdue (Red).
Instead of manually reviewing and updating status colors, we can use a formula in the symbol column to reflect the status in relation to today’s date and the task’s Due Date.
(If you haven’t yet, check out our post on Nested IF formulas and Harvey balls... we love making life easier with automation and formulas.)
Here’s a quick review of some IF formula basics:
- The basic IF() syntax (the structural pattern in the formula) is: IF(logical_test, value_if_true, value_if_false).
- Nested formulas are multi-layered and communicate with multiple columns in your sheet. Each piece of this communication is nested within the formula.
- As you build nested formulas like these, we recommend testing smaller sections of the formula before moving on to the next stage, to make sure everything is working properly.
Here’s the sheet we’ll use for this example:
In our process, a Red ball shows that we’re past the due date for a task, Yellow means that we’re within three days of the due date for a task, and Green means the task is more than three days out.
What you want to do is build a formula so if a task is not complete, the RYG ball will reflect Red, Yellow, or Green depending on how close the task is to its due date.
- Start with the basic syntax for an IF statement – IF(logical_test, value_if_true, value_if_false)
- Update the formula so that it checks to see if a task is marked complete: =IF(Complete3 <> 1, "True"). Now apply the formula to the Status column of your sheet.
- This formula looks to see if the Complete column is unchecked yet (a completed checkbox = 1). If the checkbox has not been checked in the Complete column, the formula will add the text “True” in the cell.
- Notice that we don’t have a comma followed by a value_if_false built into this first step in the formula. You can leave this off of the formula if you don’t want it to take any more steps.
- Now, update the formula to check to see how close the task is to its due date, relative to today’s date, and replace “True” with the formula logic that sets the appropriate ball colors.
- First, build the code for Red balls. How do you tell your sheet if a task is past its due date? By looking to see if today’s date minus the due date is a positive number.
=IF(Complete3 <> 1, IF(TODAY() - [Due Date]3 > 0, "Red", "Next IF"))
NOTE: “Next IF” is added here as a placeholder for the steps for Yellow and Green balls.
- Lastly, replace “Next IF” with code to record a Yellow ball if we’re within three days (or on the due date). Otherwise, a Green ball will appear to reflect that we have three days or more to complete that particular task.
=IF(Complete3 <> 1, IF(TODAY() - [Due Date]3 > 0, "Red", IF(TODAY() - [Due Date]3 > -3, "Yellow", "Green")))
All it takes is a little bit of time working with formulas to automate the processes you have in your sheets – then let your sheet do the work!
To learn more about formulas in Smartsheet, be sure to check out our Smartsheet Formula Examples sheet in the Template Gallery or watch this quick video.
Til next week, dig in and explore!
- Support Team