How to calculate Average Handling Time using Excel formula

[El chapo]

Hello everyone,

Hope you are having a great week.

I am trying to figure out a way to calculate AHT (Average Handling time) for each cases irrespective of the dates. Just to summarize the table, DE stands for Data Entry. Average handling time is calculated as the total time taken to complete a specific case (for instance, AHT for Case 1 is 3.27 hrs). In situations where a specific case extends to the next day or if additional tasks comes in between Data Entry (e.g. Case 2, 4, 5, 6, 7 ,9 in the below table), there has to be a formula to calculate the AHTs in such cases. I have attached the excel sheet for reference.

Can anybody help me calculate AHT for the below table?

Thanks in advance.

[AliGW]

Welcome to the forum.

Please add (manually) at least 10 rows of expected results in the AHT column and post the workbook again so that we know what we are aiming for.

[El chapo]

Hi AliGW,

Thanks for the response.

I have uploaded the workbook with the values as you mentioned.

Also, I would like to inform you that AHT has to be calculated only for Data Entry (DE) tasks when a specific case is "completed" in status column and additional tasks such as meeting, query resolution etc has to be excluded from AHT calculation.

Can you please assist?

[menem]

Please try this formula in I2

Please Login or Register  to view this content.

By the way , this formula got error for case 7.
Please review in cell C26 , E26 , I think date format might be the cause.

PS. This formula's result will be wrong if any of case is not completed on DE is presented.
If your data may contains these type of data please upload a new sample file.

Regards.

[El chapo]

Hi menem,

Thank you for the response. As you said the formula got error for case 7 and below. I fixed the date format issue in C26 & E26 but I am still getting error.

As you mentioned about DE presented as not completed, it is never an issue since the DE has to have a status completed. No cases are left incomplete.

Thanks you.

[menem]

I was try to copy date from row 25 to 26 , seem formula is ok. (I can not enter directly due to my localc may different)

And this is another method. please try if you can add a helper column.

helper column K

K2 ( copy until the end of data )

Please Login or Register  to view this content.

I2

Please Login or Register  to view this content.

** I can not bring formula in K2 into I2 directly , it's got error and I don't know why it is. T_T

Regards.