Forum Discussion

MeaganMcOlin's avatar
MeaganMcOlin
Qrew Cadet
7 days ago

Help with Calculating Average Completion Time Over Several Records

Hello,

I have a table where each employee progresses through a stage with three date fields: Start Date & Scope/Role, Verify Eligibility & Funds, and Create Visa Plan. I also have a formula field that tracks the status of this stage (Not Started, In Progress, Complete) and calculates the number of days taken if the stage is complete.

I need help calculating the average number of days it takes for employees to complete the stage.

For example, if Employee 1 took 14 days, Employee 2 hasn't started yet, Employee 3 took 21 days, and Employee 4 is In Progress, I want to calculate the average completion time, which would be 17.5 days (it should only count those completed).

Can anyone suggest the best way to achieve this?
Thanks so much!
Meag

 

formulas
  • Mez's avatar
    Mez
    Qrew Assistant Captain
    7 days ago

    If you only need to display this data within this table on a report, you can create a new formula duration field so all records have this, or create a report formula duration field just for the report, and then use a filter for those records that are 'completed'. 

    If you need to do more with this data, you can consider a summary table (then use summary fields with conditions, and create a formula field to perform the average from these summary fields), or on this table create formula fields where you're using formula queries (complicated and dependent on size of records could cause performance issues) to obtain counts of those with a completed status - which will be easy if you also have a formula field dedicated to this. 

    It looks like you might have a formula field that is doing both things: status calc, and days calc. It might be best to have three fields in total: 1. calc status, 2. calc days, 3. display what you currently have for both (in your image).

    Example using a duration field table level: