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

jmyzk_cloudsmart_jp

Hi @mill_user

To correctly display the Harvey Ball colors based on status and dates, you may want to update your formula to better reflect your intended criteria. For example, you might choose to treat "Complete" as Green regardless of whether it’s overdue — though some teams may prefer overdue completed tasks to show a different color. Similarly, assigning "Gray" only when a task hasn't started and is scheduled for the future is just one possible interpretation. These criteria are suggestions and can be adjusted based on your team's specific needs or definitions.

Recommended 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 = "Not Started" and [Start Date] < TODAY() and [End Date] >= TODAY() → Should have started but hasn’t
• Gray
  • Status = "Not Started" and [Start Date] > TODAY() → Task is scheduled in the future

Suggested Formula:

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

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

    IF(Status@row = "In Progress",

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

      IF(Status@row = "Not Started",

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

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

        ),

        "Gray"

      )

    )

  )

)

This will ensure each status is correctly reflected by color based on timing and condition.

https://app.smartsheet.com/b/publish?EQBCT=66d0cb4b448345808981a1523e4a207e

mill_user

@jmyzk_cloudsmart_jp - Thanks for this. One concern I see with the formula you've provided is that for a row, wherein start date is in the past , end date is in the future , and status is "At Risk", the harvey ball is showing grey but ideally, should show yellow as status is <> "In Progress"

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

mill_user

Seems like it fits all the use cases. Thanks for all the help!

jmyzk_cloudsmart_jp

Happy to help!😁