How to create a detailed performance overview (Maybe a graph/chart)

[Laagi]

Hello everyone,

I am currently trying to create a detailed overview of transactions executed by a range of different users in the hopes of creating a easy to understand overview of performance. Which will include the points listed below.

In this performance overview I want to include several factors.

• Time spent idle (gaps larger than Xminutes) if I understand correctly, eliminating all idle time will then give me an overview of total time spent active.
• Total tasks executed per day (One task = one pick, so between 08:27:16 to 08:50:46 ID7638 executed 6 tasks) - Referring to highlighted time in example listed below
• Total tasks executed hourly
• Average tasks executed hourly & daily (Over a weekly period) Once I have found the formula for the
• Average minutes used per task (Over a daily period)

An example of a transaction history of a user would be;

Task	Date	Time	Qty	User
Pick	06/01/2023	08:27:16	1	ID7638
Pick	06/01/2023	08:28:36	1	ID7638
Pick	06/01/2023	08:47:48	1	ID7638
Pick	06/01/2023	08:47:57	1	ID7638
Pick	06/01/2023	08:49:43	1	ID7638
Pick	06/01/2023	08:50:46	1	ID7638
Pick	06/01/2023	08:54:52	1	ID7638
Pick	06/01/2023	08:55:06	1	ID7638
Pick	06/01/2023	08:56:47	1	ID7638
Pick	06/01/2023	08:57:07	1	ID7638
Pick	06/01/2023	09:07:29	1	ID7638
Pick	06/01/2023	09:18:00	1	ID7638
Pick	06/01/2023	09:38:51	1	ID7638
Pick	06/01/2023	09:44:26	1	ID7638
Pick	06/01/2023	09:44:35	1	ID7638
Pick	06/01/2023	09:44:45	1	ID7638
Pick	06/01/2023	09:34:55	1	ID7638
Pick	06/01/2023	09:45:04	1	ID7638
Pick	06/01/2023	10:45:15	1	ID7638
Pick	06/01/2023	10:45:26	1	ID7638

Does anyone know how I can achieve my goal of creating this overview, the example listed below is a very small example compared to the range of transactions executed throughout a day. I am dealing with 20+ users where each of them perform at least 1000 tasks a day. This example is made to demonstrate the information I am given when searching up the history of transactions made by a given user.

I am hoping to be able to create a graph or chart representing the performance as well, if possible. My biggest obstacle with this task is finding a way for excel to help me search for time gaps over X minutes over a range of thousands of transactions.

Excel has so many functions which I am still trying to wrap my head around, hopefully some of you can give me insight as to how I can manage to set this up

Thank you for reading, I am looking forward to exploring this with everyone.

 

[Laagi]

Hello again,
So I was just thinking about solutions and then it occurred to me that if I have excel helping me find gaps greater than 10 minutes it will highlight too many gaps that don't correlate with what I am looking for. Say for example that picks were executed at [ 08:23, 08:24, 08:25 and so on ] it would highlight everything considering the fact that all times have a specific time where the 10 minute gap is exceeded. So for this to work I need to find out how to only look for gaps between two rows that are beneath or above each other.

Book1
	A	B	C	D	E
1	Task	Date	Time
2	Pick	05/01/2023	15:46:35
3	Pick	05/01/2023	15:48:16
4	Pick	05/01/2023	15:50:18
5	Pick	05/01/2023	15:51:58
6	Pick	05/01/2023	15:52:25
7	Pick	05/01/2023	15:53:03
8	Pick	05/01/2023	15:53:29
9	Pick	05/01/2023	15:53:47
10	Pick	05/01/2023	15:54:09
11	Pick	05/01/2023	15:57:40
12	Pick	05/01/2023	15:58:03
13	Pick	05/01/2023	15:58:19
14	Pick	05/01/2023	15:59:59
15	Pick	05/01/2023	16:00:51	This is what I need highlighted
16	Pick	05/01/2023	16:12:19
Sheet1

I am unsure as to how I can put in a criteria that states if a time gap of 10 minutes or more is found between two rows it is to be highlighted but if the number of rows exceeds 1 then false. That rule will then prevent the other timestamps of being highlighted unless the gap of 10 minutes is found within 2 rows.