Harvey balls formula

mill_user

I'm trying to denote row status using harvey balls of 4 colors - red, yellow, orange and grey.

1. Red - Blocked or end date is past
2. Yellow - Start date is past AND status is not In Progress
3. Green - End date is in the future
4. Gray - Not started

This is my formula -

=IF(Status@row = "Not Started", "Gray", IF(OR(Status@row = "Blocked", [End Date]@row < TODAY()), "Red", IF(OR(Status@row = "Not Started", [Start Date]@row < TODAY()), "Yellow", IF([End Date]@row > TODAY(), "Green"))))

But, for a row that has status complete, start date and end date in the past, the harvey ball is red. How should I modify my formula?

jmyzk_cloudsmart_jp

Hi @mill_user

Thanks for sharing the details! Based on your original request and the new update regarding the "At Risk" status scenario, we can adjust the logic to include any status that is not "In Progress"—specifically to flag them yellow if they should have started already.

Here's an updated version of the formula that handles:

=IF(Status@row = "Complete", "Green",

  IF(Status@row = "Blocked", "Red",

    IF(Status@row = "In Progress",

      IF([End Date]@row < TODAY(), "Red", "Green"),
 IF(AND(Status@row <> "In Progress", [Start Date]@row <= TODAY(), [End Date]@row >= TODAY()), "Yellow",

        IF(Status@row = "Not Started",

          IF([End Date]@row < TODAY(), "Red",

            IF([Start Date]@row > TODAY(), "Gray", "Yellow")

          ),

          "Gray"

        )

      )

    )

  )

)

https://app.smartsheet.com/b/publish?EQBCT=77a7c68644f9475997d8bbd3a4641fb0

Updated Logic:

Green

• Status = "Complete" → Always Green
• Status = "In Progress" and [End Date] >= TODAY() → On track
• Status = "In Progress" and [Start Date] > TODAY() → Started early

Red

• Status = "Blocked" → Always Red
• Status = "Not Started" and [End Date] < TODAY() → Overdue and not started
• Status = "In Progress" and [End Date] < TODAY() → In progress but overdue

Yellow

• Status ≠ "In Progress" and [Start Date] < TODAY() and [End Date] >= TODAY() → Should have started but hasn’t (includes statuses like "Not Started" or "At Risk")

Gray

• Status = "Not Started" and [Start Date] > TODAY() → Task is scheduled in the future
• All other cases not covered above → Default to Gray