Calculating Average Time for each member of a team

Henry Tam Tran-Quevedo · 07/21/21

Hi everyone,

I am new to SmartSheets and Excel formulas in general and would like some help please. I would like to calculate the average time in months that it takes for a team member to complete tasks. For example, I have a date created column for every task and another column named "Team member". I would like to calculate the number of months since the task was created from today and obtain that number for every task for that corresponding team member and calculate the overall average for all of the tasks in months for every team member.

I currently have " =(NETDAYS(({Reference}, {Reference})/ 365) * 12 " to calculate the number of months for a specific cell, but I would like to do it for all the tasks corresponding to a specific team member.

Any help is greatly appreciated.

Bassam Khalil · 07/22/21

Hi @Henry Tam Tran-Quevedo

Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

Henry Tam Tran-Quevedo · 07/23/21

Hi Bassam,

Thanks for replying. Say something like this. For every team member, I would like to get the average time (in months) for all of the tasks corresponding to that specific member with the "In Progress" status. As an example, get the number of months since "Task1" was created from today and do that for all of Robert's tasks and then get the average time(In months) for Robert so that I can calculate Robert's overall average aging that includes all of his tasks that are in progress.

Note: In my screen shot all tasks were created today, but in the actual sheet, all tasks have different dates from many months ago.

Thank you again. I appreciate the help.

Screen Shot 2021-07-23 at 8.58.21 AM.png

Henry Tam Tran-Quevedo · 07/27/21

https://community.smartsheet.com/discussion/comment/298669#Comment_298669

Calculating Average Time for each member of a team

Answers

Help Article Resources

Categories