Tracking Average Time in Overdue Status

English Forum Tips and Tricks
, , , , , , ,
1

Hello!

We rarely post here because we do our best to find solutions on our own or by searching through the forums. This approach has helped us solve hundreds of issues in the past. However, this time, we’ve hit a roadblock we can’t seem to figure out, so we’re reaching out for advice.

Our Goal:

  1. Track total overdue time for each task (how many days a task is overdue over its lifetime).
  2. Calculate the average overdue time for all tasks in a project (e.g., if 1 task is overdue for 2 days and 9 tasks are never overdue, the average is 0.2 days).

What We Tried:

  • Added a custom field, “Overdue Status” (options: “On Time” and “Overdue”).
  • Created rules to set:
    • “On Time” when the due date is updated.
    • “Overdue” when a task becomes overdue.
  • Used a dashboard chart to track “Time in Custom Field” for “Overdue.”

The Issue:

The chart only tracks tasks that were overdue at some point. Tasks never overdue aren’t included, which skews the average.

Any ideas on how to fix this? Thanks!

1 Like
2

Additional Context:

  • In this project, tasks are not one-off. Each task represents a case that is usually worked on one or multiple times a week for 3–5 months.
  • We use “Due Date” as the “Next Action Date,” indicating when the task needs to be worked on again.
  • Once a task is worked on, a new Due Date is set for the next action.

This means a single case (task) can enter overdue status multiple times, making it crucial to track how long it remains overdue and calculate the average overdue time per case. This data provides valuable insights into capacity, workload, delays, and areas for optimization.

Any ideas on how to fix this? Thanks!