Nested IF's Based on Date and Additional Criteria

Adam Enright

Hi All,

 

I am trying to craft a formula that will help automate the pacing of tasks. I have four columns: Task Name (Text), Task Completed (Checkbox), Task Status (Green, Red, Yellow, Blue), and Task Due Date (Date).

                                  

For Example. [Determine Target Audience] - [Checked] - [Green] - [1/24/2017]

 

I am trying to automate "Task Status" where:

 

-Green if "Task Completed" is checked (regardless of date)

-Yellow if "Due Date" is less than five days from TODAY, and "Task Completed" is NOT checked

-Red if "Due Date" TODAY or earlier, and "Task Completed" is NOT checked

-Blue if "Due Date" is greater than five days from TODAY, and "Task Completed" is NOT checked

 

If anyone could help me in generating a nested IF statement that would be greatly appreicated. 

 

Shaine Greenwood

Hello Adam,

 

When building your formula, it's best to start a section at a time, see if it works, then add on to that section.

 

I'd recommend starting with the "Green" criteria first, then going to the others. Formulas are read in Smartsheet from left to right, and will stop calculating to return a result once the IF statement finds a "true" criteria.

 

You'll want to also utilize the TODAY() function, see our Functions List for more information.

 

Lastly, make sure that all of your parens for the IF() function close at the very end of the formula, e.g. =IF(CRITERIA, IF(CRITERIA))

 

Check out our Functions List for more robust examples of IF statements.

 

 

 

Jeremy Myers

Adam:  

 

Here are the bits and pieces in raw form, not proof read..  Build them up one piece at a time as noted by Shane, above.  Fiddle around with the values until you get them working piece by piece and then string them all together with commans in between.  

 

If your color ball indicators are operating opposite of the way you anticipate, just reverse the results at the end string of hte formula from "Blue", "") to "", "Blue"  or similar.

 

If you need to exclude Saturday and Sunday as workdays, insert the function NETWORKDAYS in front of the TODAY function as follows:   NETWORKDAYS(TODAY()

 

I would create a separte Hidden Column called "Date Start" or similar to hold the formula and then have your color ball column point to that to read the result.  Your color ball column cell would then contain the following   =[Date Start]7 

 

1.  Green if task completed regardless of date:

=IF(Checkbox1, "Green")

 

2.  -Yellow if "Due Date" is less than five days from TODAY, and "Task Completed" is NOT checked

=(IF(AND([Due Date]7 = TODAY(), [Due Date]7 <= TODAY() + 3), IF(Checkbox7, "Yellow", "")))

 

3.  -Red if "Due Date" TODAY or earlier, and "Task Completed" is NOT checked

=IF(AND([Due Date]7 <= TODAY(), Checkbox7), "", "Red", "")

 

4. -Blue if "Due Date" is greater than five days from TODAY, and "Task Completed" is NOT checked

=(IF(AND([Due Date]7 >= TODAY(),+5), IF(Checkbox7, "Blue", "")))

15UZU

Hi Adam

 

I'm not 100% sure whether the technique will work in Smartsheet, but in Excel you can make nested IF() statements much easier to manage (and get around the nesting limit) by instead using the SUM() function with a series of IF() statements inside.

Each IF() statement should only return a value for a TRUE condition, and return a NULL (or "") value for false. Be mindful this method can only be used if you can be sure you'll never have more than one of the IF()'s return a TRUE condition.