How to compute Average turn-around time of tickets

[Fanim]

Hey Folks! Please help me to compute the average turn around time of tickets at the time ticket is open and close.

I have this formula =INT(D2-C2)&" days "&TEXT(D2-C2,"h"" hrs ""m"" mins """)

D2 is the date and time resolved
C2 Date and time reported

I was able to get the hours and mins with that formula, however my problem is how will i compute the total hours in oder to divide to the total number of tickets so i can get the average turn around time.

Number Opened State Actual Resolved By Actual Resolved On
INC1634085 12-12-2019 17:19:08 Closed DEODORES 20-10-2020 17:32:35
INC1636002 08-05-2020 10:19:18 Closed Domingo 18-09-2020 15:01:54
INC1636060 15-04-2020 20:54:37 Resolved Domingo 03-09-2020 22:35:21
INC1636089 11-05-2020 08:39:59 Closed DEODORES 13-10-2020 14:21:09
INC1636258 11-06-2020 16:29:31 Closed DEODORES 18-09-2020 16:13:01
INC1636536 19-06-2020 10:14:31 Closed Soledad 19-10-2020 16:09:39


We have 214 total tickets

Pls help meeee!

[Glenn Kennedy]

Hi there.

A picture is worth 1,000 words. An Excel sheet is worth 1,000 pictures.

Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet.

[Fanim]

Actually I cannot attach file here. What seems to be the problem? im trying to click the attachment icon but it cant open

[Glenn Kennedy]

Re-read the instructions in the yellow banner. It does not make any mention of the attachment icon, 'cos it's broken.

[Fanim]

Ok have edited it

[alansidman]

Don't see an attached file anywhere. Please try again.

[Fanim]

Hi there! Alansidman.. Glenn says the attachment is broken. I just posted the data on my post

[Glenn Kennedy]

I said the ICON is broken I gave you instructions on how to make an attachment.

Unfortunately the attachment icon doesn't work at the moment. So,... instead click on Go Advanced (below the Edit Window) while composing your reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

[Fanim]

Apologies. Was bit confuse earlier but thanks for patiently guiding me. Have attached it.

[Fanim]

Hope someone can help me here. Thanks in advance

[AliGW]

In H2 copied down:

=(F2-B2)*24

Then:

=AVERAGE(H2:H8)

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Thanks for the rep.

[Glenn Kennedy]

An afterthought. Without the helper column:

=SUMPRODUCT(24*AVERAGE(F2:F8-B2:B8))