#### 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.

# Nested IF's Based on Date and Additional Criteria

Options
edited 12/09/19

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.

Tags:

• Employee
Options

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.

• ✭✭✭✭✭
Options

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

• ✭✭
edited 01/29/17
Options