Harvey Balls Automation

Hello,

Thanks for reaching out! You are correct that using a nested IF statement would automate your RYG ball symbol column so that the symbols change based on the referenced end date and completion status cells. 

Here is an example of what the formula might look like in your sheet:

=IF(AND([End Date]1 > TODAY(), [Completion Status]1 = "Full"), "Green", IF(AND([End Date]1 > TODAY(), [Completion Status]1 <> "Full"), "Green", IF(AND([End Date]1 < TODAY(), [Completion Status]1 = "Full"), "Green", IF(AND([End Date]1 = TODAY(-2), [Completion Status]1 <> "Full"), "Yellow", IF(AND([End Date]1 < TODAY(), [Completion Status]1 <> "Full"), "Red")))))

When used in a formula, the IF function evaluates a logical expression and returns one value when true or another when false. In this case, the function is being used with the AND function to determine whether or not a cell is Full or if the current date is greater or less than the specified end date.

Please note that I did not include an IF statement for the criteria: “if End Date is 2 days before today and Completed is full then make it green”.

That is because of the portion of the formula that reads….IF(AND([End Date]1 < TODAY(), [Completion Status]1 = "Full"), "Green”…will still cause the symbol column to turn green when the end date is 2 days before the current date.

For more information on working with the IF function, I also recommend checking out this article from our Help Center: https://help.smartsheet.com/function/if

Please let me know if you’re looking for something else and I’ll be more than happy to advise further!