Need to calculate the average amount of days it takes to complete a specific task



I am working on trying to figure out the correct formula to use to find the average amount of days it takes to complete a specific task.

I have multiple types of tasks on my sheet and created an automation that stamps the date it was completed. I need to now find a formula that will sort the average amount of time for each of my tasks separately.

Any help would be very much appreciated.


  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @JGrison,

    1. Add a new column to your sheet, let's name it "Duration".
    2. In this column, for each task, you will calculate the difference between the start date and the completion date. Assuming your start date is in column A and your completion date is in column B, the formula for each row in the Duration column (let's say this is column C) would look like:
    Duration@row==[End Date]@row - [Start Date]@row

    3- Create a column for task type call it "Task Type"

    4- use the following formula to calculate the Average for completed activities type "A" For Example

    =AVG(COLLECT(Duration:Duration, [Task Type]:[Task Type], "A", Completed:Completed, "completed"))

    the following is a screen shot for sample sheet:

    PMP Certified

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"