Harvey Balls Automation

lisa.bradley

I am trying to put together a formula to automatically change the RYG harvey balls based on the end date(due date) and the completion status of the empty/quarter/half/three quater/full harvey balls? I was hoping to get some help putting the nested if statement together.

Here are the conditions I am working with and a screenshot of the sheet I am hoping to apply it to.

if End Date is after today's date and Completed is full then make it green

if End Date is after today's date and Completed is not full then make it green

if End Date is before today's date and Completed is full then make it green

if End Date is before today's date and Completed is not full then make it red

if End Date is 2 days before today and Completed is full then make it green

if End Date is 2 days before today and Completed is not full then make it yellow

Kelsey Hayden

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!