Time overlap and reduced the overlap time

[Airam]

Hi Everyone

I have 1 employee with series of status and notice that there are status with time overlapping the other which increases the hours overall. I would like to ask your help to identify this return the the time that overlaps which I need to reduce or consider for that specific status only

Example: File attached

Line 62 has Busy status from 20:32 until 21:00 and at 20:56, there is another status , "Working on Ticket". This will add 4 minutes more to the total hours of the employee which I want to remove. Theres a lot of this in the whole list. But just sharing a sample for your assistance please.

TIme Overlapping.xlsx
	A	B	C	D	E	F	G	H	I
1	Media Type	Agent Name	Start Timestamp	End Time	State	Duration (Fmt)
2	Voice	John Doe	1/3/2023 11:57	1/3/2023 11:59	Start Of Shift	0:01:46
3	Chat	John Doe	1/3/2023 11:59	1/3/2023 11:59	Ready	0:00:22
4	Chat	John Doe	1/3/2023 11:59	1/3/2023 12:50	Busy	0:50:55
5	Voice	John Doe	1/3/2023 12:50	1/3/2023 12:52	Personal	0:02:33
6	Chat	John Doe	1/3/2023 12:52	1/3/2023 12:53	Ready	0:00:32
7	Chat	John Doe	1/3/2023 12:53	1/3/2023 13:04	Busy	0:11:11
8	Chat	John Doe	1/3/2023 13:04	1/3/2023 13:07	Ready	0:02:43
9	Chat	John Doe	1/3/2023 13:07	1/3/2023 13:16	Busy	0:09:14
10	Chat	John Doe	1/3/2023 13:16	1/3/2023 13:19	Ready	0:02:38
11	Voice	John Doe	1/3/2023 13:19	1/3/2023 13:22	Personal	0:03:37
12	Chat	John Doe	1/3/2023 13:22	1/3/2023 13:27	Ready	0:05:02
13	Chat	John Doe	1/3/2023 13:27	1/3/2023 13:41	Busy	0:14:12
14	Chat	John Doe	1/3/2023 13:41	1/3/2023 13:45	Ready	0:03:42
15	Chat	John Doe	1/3/2023 13:45	1/3/2023 13:53	Busy	0:08:08
16	Chat	John Doe	1/3/2023 13:53	1/3/2023 13:54	Ready	0:00:48
17	Chat	John Doe	1/3/2023 13:54	1/3/2023 14:06	Busy	0:12:02
18	Chat	John Doe	1/3/2023 14:06	1/3/2023 14:08	Ready	0:01:25
19	Chat	John Doe	1/3/2023 14:08	1/3/2023 14:22	Busy	0:14:09
20	Chat	John Doe	1/3/2023 14:22	1/3/2023 14:23	Ready	0:01:22
21	Voice	John Doe	1/3/2023 14:23	1/3/2023 14:25	Personal	0:02:08
22	Voice	John Doe	1/3/2023 14:23	1/3/2023 14:25	Meeting	0:02:08
23	Chat	John Doe	1/3/2023 14:25	1/3/2023 14:28	Ready	0:02:47
24	Chat	John Doe	1/3/2023 14:28	1/3/2023 15:11	Busy	0:42:33
25	Voice	John Doe	1/3/2023 15:11	1/3/2023 15:15	Personal=Personal	0:04:43
26	Chat	John Doe	1/3/2023 15:15	1/3/2023 15:18	Ready	0:02:53
27	Chat	John Doe	1/3/2023 15:18	1/3/2023 15:50	Busy	0:32:05
28	Voice	John Doe	1/3/2023 15:42	1/3/2023 16:13	Working on Ticket	0:30:13
29	Voice	John Doe	1/3/2023 15:42	1/3/2023 16:13	Break	0:30:13
30	Voice	John Doe	1/3/2023 15:42	1/3/2023 16:13	Admin Works	0:30:13
31	Chat	John Doe	1/3/2023 16:13	1/3/2023 16:13	Ready	0:00:41
32	Chat	John Doe	1/3/2023 16:13	1/3/2023 16:18	Busy	0:04:35
33	Chat	John Doe	1/3/2023 16:18	1/3/2023 16:22	Ready	0:04:31
34	Chat	John Doe	1/3/2023 16:22	1/3/2023 16:35	Busy	0:13:01
35	Chat	John Doe	1/3/2023 16:35	1/3/2023 16:35	Ready	0:00:02
36	Voice	John Doe	1/3/2023 16:35	1/3/2023 16:36	Meeting=Meeting	0:00:13
37	Chat	John Doe	1/3/2023 16:36	1/3/2023 16:43	Ready	0:07:05
38	Chat	John Doe	1/3/2023 16:43	1/3/2023 16:50	Busy	0:07:01
39	Chat	John Doe	1/3/2023 16:50	1/3/2023 16:50	Ready	0:00:11
40	Voice	John Doe	1/3/2023 16:50	1/3/2023 17:06	Meeting	0:16:06
41	Voice	John Doe	1/3/2023 16:50	1/3/2023 17:06	Training	0:16:06
42	Chat	John Doe	1/3/2023 17:06	1/3/2023 17:12	Ready	0:05:40
43	Chat	John Doe	1/3/2023 17:12	1/3/2023 17:34	Busy	0:22:15
44	Chat	John Doe	1/3/2023 17:34	1/3/2023 17:39	Ready	0:04:54
45	Voice	John Doe	1/3/2023 17:39	1/3/2023 17:43	Personal	0:04:24
46	Chat	John Doe	1/3/2023 17:43	1/3/2023 17:44	Ready	0:00:39
47	Voice	John Doe	1/3/2023 17:44	1/3/2023 18:19	Personal	0:34:58
48	Voice	John Doe	1/3/2023 17:44	1/3/2023 18:19	Lunch	0:34:58
49	Chat	John Doe	1/3/2023 18:19	1/3/2023 18:29	Ready	0:10:11
50	Chat	John Doe	1/3/2023 18:29	1/3/2023 18:54	Busy	0:25:19
51	Chat	John Doe	1/3/2023 18:54	1/3/2023 19:03	Ready	0:08:42
52	Chat	John Doe	1/3/2023 19:03	1/3/2023 19:17	Busy	0:13:50
53	Chat	John Doe	1/3/2023 19:17	1/3/2023 19:20	Ready	0:03:04
54	Chat	John Doe	1/3/2023 19:20	1/3/2023 19:28	Busy	0:07:55
55	Chat	John Doe	1/3/2023 19:28	1/3/2023 19:33	Ready	0:05:27
56	Chat	John Doe	1/3/2023 19:33	1/3/2023 19:50	Busy	0:16:18
57	Chat	John Doe	1/3/2023 19:50	1/3/2023 19:53	Ready	0:03:22
58	Chat	John Doe	1/3/2023 19:53	1/3/2023 20:01	Busy	0:07:47
59	Voice	John Doe	1/3/2023 20:00	1/3/2023 20:21	Working on Ticket	0:21:28
60	Voice	John Doe	1/3/2023 20:00	1/3/2023 20:21	Break	0:21:28
61	Chat	John Doe	1/3/2023 20:21	1/3/2023 20:32	Working on Ticket	0:10:17
62	Chat	John Doe	1/3/2023 20:32	1/3/2023 21:00	Busy	0:28:07
63	Voice	John Doe	1/3/2023 20:56	1/3/2023 21:00	Working on Ticket	0:04:07
Sheet1

Cell Formulas
Range	Formula
D2:D63	D2	=IF(G2>0,C2+G2,C2+F2)

 

[Anthony47]

So the whole data belong to the same employee, and the table is in chronological order; then it should be enough setting two conditional formats:
-select the whole table, from row 2 to the end (or even more)
-apply a conditional format, with the clause "Use a formula", using the formula =$D2>$C3 and applying the preferred filling color
-add a second format, again "Use a formula", using the formula =$C2<$D1 and apply the same filling color

These should highlight the row where the new activity starts before the end of the previous one and its companion row
This will improperly highlight the first line of data; if this is a problem then use for the second formatting the formula =AND(ROW(C2)>2,$C2<$D1)