Long formula for Status balls with 2 criteria

RGarrels

I have a tracker that needs a Status column using Harvey balls.

There are columns for 6 mon, 12 mon, and 24 mon MIN and MAX and Completed Visit scheduling dates, and the Project Manager is asking for a Status column that will show whether there is a visit past due, coming up, or completed based on the colored balls.

(I struggle with creating complex formulas in the right order so that they work).

The formula needs to:

Paul Newcome

Give this a try:

I would start by inserting a date type column (called "Next Visit" in this example) and then use this formula...

=IF([Database Status]@row <> "Early Withdrawal", IF([Completed 6]@row = "", [Max 6]@row, IF([Completed 12]@row = "", [Max 12]@row, IF([Completed 24]@row = "", [Max 24]@row))))

This should give you the date of the next visit and be blank if it is either early withdrawal or the 24 month has been completed.

This should greatly simplify the Harvey Ball formula to something along the lines of...

=IF([Database Status]@row = "Early Withdrawal", "Blue", IF([Next Visit]@row = "", "Green", IF([Next Visit]@row< TODAY(), "Red", IF([Next Visit]@row<= TODAY(30), "Yellow"))))

This will be blank for any rows where the next visit is more than 30 days in the future. If you wanted to add a color for that, you would drop this in after "Yellow" and before the closing parenthesis:

"Yellow", "color for 31+ days"))))

Paul Newcome

Give this a try:

I would start by inserting a date type column (called "Next Visit" in this example) and then use this formula...

=IF([Database Status]@row <> "Early Withdrawal", IF([Completed 6]@row = "", [Max 6]@row, IF([Completed 12]@row = "", [Max 12]@row, IF([Completed 24]@row = "", [Max 24]@row))))

This should give you the date of the next visit and be blank if it is either early withdrawal or the 24 month has been completed.

This should greatly simplify the Harvey Ball formula to something along the lines of...

=IF([Database Status]@row = "Early Withdrawal", "Blue", IF([Next Visit]@row = "", "Green", IF([Next Visit]@row< TODAY(), "Red", IF([Next Visit]@row<= TODAY(30), "Yellow"))))

This will be blank for any rows where the next visit is more than 30 days in the future. If you wanted to add a color for that, you would drop this in after "Yellow" and before the closing parenthesis:

"Yellow", "color for 31+ days"))))

RGarrels

Perfect!! Thank you!

Paul Newcome

Happy to help. 👍️