FILTER function looking at duration and time

[dancer5]

Hello, I'm trying to modify a filter function I'm already using and have no idea where to start. Right now I have a very large data set and the filter function is looking for all occurrences >23hrs. What I'm trying to find now is all instances where a person has >23hrs over a 26hr time span. I have start dates and times and durations.

This is my current filter formula in A4 and resultant dataset. It works for finding >23hr:

duration temp.xlsx
	A	B	C	D	E	F	G
1	23		all sites
2		First Blank	25966
3	ID	Type	Episode Date dd-mmm-yyyy	Episode time hh:mm	hh	mm	ss
4	001-003	A	10-Sep-2019	14:48	475	56	21
5	001-003	A	29-Apr-2021	9:02	29	29	35
6	001-003	A	29-Apr-2021	9:02	29	29	35
7	001-008	A	30-Dec-2019	5:29	166	53	21
8	001-008	A	6-Jan-2020	4:39	31	47	39
9	001-014	A	15-Oct-2020	14:41	25	13	8
10	001-021	A	3-Apr-2021	11:33	29	10	35
11	001-021	A	4-Apr-2021	16:44	111	40	58
12	001-021	A	9-Apr-2021	8:30	47	54	59
13	001-021	A	11-Apr-2021	8:25	122	58	27
14	001-021	A	16-Apr-2021	11:24	47	55	45
15	001-021	A	27-Apr-2021	0:04	26	48	27
16	001-021	A	1-May-2021	16:46	162	3	58
17	001-021	A	8-May-2021	10:51	268	35	2
18	001-021	A	19-May-2021	15:27	70	23	25
19	001-021	A	22-May-2021	13:50	169	28	7
20	001-027	A	30-Apr-2021	6:57	25	24	19
21	001-027	A	18-May-2021	22:18	29	1	27
22	001-027	A	27-May-2021	23:10	36	22	23
23	001-033	A	6-Dec-2021	4:51	30	2	6
24	001-033	A	13-Dec-2021	22:06	44	59	1
25	001-033	A	26-Dec-2021	0:38	36	14	29
26	001-033	A	2-Jan-2022	8:00	30	23	53
27	001-033	A	6-Jan-2022	19:21	26	15	19
28	001-033	A	10-Jan-2022	3:37	31	15	13
29	001-033	A	13-Jan-2022	2:31	28	59	22
30	001-033	A	19-Jan-2022	0:03	36	3	6
31	001-033	A	22-Jan-2022	9:39	24	54	21
32	001-033	A	25-Jan-2022	9:17	53	46	2
33	001-033	A	30-Jan-2022	16:23	44	0	28
34	001-033	A	4-Feb-2022	19:11	40	38	25
35	001-036	A	16-Jan-2022	8:18	45	0	7
36	001-036	A	18-Jan-2022	5:42	24	49	43
37	001-036	A	26-Feb-2022	7:40	99	28	14
38	002-006	A	6-Apr-2022	8:31	42	11	14
39	002-006	A	8-Apr-2022	8:11	26	7	58
40	002-006	A	15-May-2022	12:02	42	31	34
41	002-006	A	17-May-2022	8:38	69	45	11
42	002-006	A	20-May-2022	7:50	26	47	45
43	002-006	A	25-May-2022	7:59	105	24	50
44	002-006	A	30-May-2022	8:18	46	7	29
45	002-006	A	2-Jun-2022	7:02	72	6	19
46	002-006	A	5-Jun-2022	10:42	44	7	29
47	002-006	A	17-Jun-2022	8:49	24	55	18
48	004-001	A	19-Mar-2022	6:55	25	58	41
49	007-007	A	17-Dec-2022	4:08	25	11	39
50	009-005	A	28-Oct-2022	0:44	59	5	26
51	009-005	A	16-Dec-2022	0:36	31	51	15
52	009-005	A	17-Dec-2022	15:17	264	33	35
53	011-011	A	26-Sep-2022	12:25	35	5	37
54	012-001	A	11-Jun-2022	2:46	38	42	59
55	012-004	A	3-Nov-2022	20:19	29	22	47
56	012-004	A	13-Nov-2022	22:32	42	19	49
57	015-001	A	10-May-2021	10:42	24	5	20
Sheet1

Cell Formulas
Range	Formula
C2	C2	=(MIN(IF('all sites'!B5:B29891="",ROW('all sites'!B5:B29891))))-1
A4:G57	A4	=FILTER('all sites'!A5:INDIRECT("'all sites'!G"&C2), IFERROR('all sites'!E5:INDIRECT("'all sites'!E"&C2),0)>A1, 0)
Dynamic array formulas.

And a sample of my data set:

ID	Type	Episode Date dd-mmm-yyyy	Episode time hh:mm	hh	mm	ss
001-002	A	14-Oct-2019	1:28	17	34	8
001-002	A	12-Oct-2020	23:40	0	17	8
001-002	A	5-Nov-2020	13:24	0	8	41
001-002	A	5-Nov-2020	23:35	#VALUE!	#VALUE!	#VALUE!
001-002	A	14-Nov-2020	18:29	1	59	21
001-002	A	14-Nov-2020	18:29	1	59	21
001-002	A	30-Jan-2021	2:18	#VALUE!	#VALUE!	#VALUE!
001-002	A	8-Feb-2021	9:26	0	20	1
001-002	A	15-Feb-2021	11:35	0	23	43
001-002	A	27-Apr-2021	11:54	#VALUE!	#VALUE!	#VALUE!
001-002	A	16-May-2021	19:10	0	6	11
001-002	A	22-May-2021	22:29	#VALUE!	#VALUE!	#VALUE!
001-002	A	24-May-2021	15:31	#VALUE!	#VALUE!	#VALUE!
001-002	A	24-May-2021	15:39	#VALUE!	#VALUE!	#VALUE!
001-002	A	24-May-2021	17:19	0	6	9
001-002	A	24-May-2021	17:43	0	9	33
001-002	A	28-May-2021	17:46	#VALUE!	#VALUE!	#VALUE!
001-003	A	10-Sep-2019	14:48	475	56	21
001-003	A	7-Oct-2019	3:03	#VALUE!	#VALUE!	#VALUE!
001-003	A	29-Oct-2019	10:11	0	20	24
001-003	A	18-Nov-2019	12:59	0	11	0
001-003	A	26-Nov-2019	12:02	#VALUE!	#VALUE!	#VALUE!
001-003	A	4-Dec-2019	12:04	0	10	33
001-003	A	4-Dec-2019	12:16	0	13	14
001-003	A	4-Dec-2019	13:40	0	13	6
001-003	A	5-Dec-2019	12:50	0	6	34
001-003	A	5-Dec-2019	12:59	0	28	17
001-003	A	5-Dec-2019	13:51	0	46	3
001-003	A	7-Dec-2019	11:54	0	30	53
001-003	A	9-Dec-2019	21:05	#VALUE!	#VALUE!	#VALUE!
001-003	A	16-Dec-2019	11:00	0	14	6
001-003	A	16-Dec-2019	11:20	0	6	16
001-003	A	17-Dec-2019	13:19	0	12	30
001-003	A	17-Dec-2019	14:15	0	14	14
001-003	A	17-Dec-2019	16:58	0	9	54
001-003	A	18-Dec-2019	13:10	0	59	49
001-003	A	23-Dec-2019	11:08	0	37	40
001-003	A	24-Dec-2019	10:26	0	15	7
001-003	A	27-Dec-2019	22:20	0	9	11
001-003	A	29-Dec-2019	21:47	0	21	54
001-003	A	31-Dec-2019	9:00	0	10	46
001-003	A	1-Jan-2020	11:38	0	22	45
001-003	A	1-Jan-2020	17:00	0	13	43
001-003	A	1-Jan-2020	20:07	0	27	20
001-003	A	13-Sep-2020	15:54	0	27	17
001-003	A	13-Sep-2020	16:22	1	43	28
001-003	A	13-Sep-2020	18:20	0	11	11
001-003	A	13-Sep-2020	18:33	0	6	2
001-003	A	13-Sep-2020	18:40	0	9	40
001-003	A	13-Sep-2020	18:51	0	18	44
001-003	A	13-Sep-2020	19:16	0	13	36
001-003	A	13-Sep-2020	19:31	0	18	18
001-003	A	13-Sep-2020	19:51	0	12	42
001-003	A	13-Sep-2020	20:04	0	47	3
001-003	A	13-Sep-2020	20:55	0	24	33
001-003	A	13-Sep-2020	21:21	4	21	27
001-003	A	14-Sep-2020	1:48	2	38	40
001-003	A	14-Sep-2020	4:30	3	35	4
001-003	A	14-Sep-2020	8:12	4	0	53
001-003	A	14-Sep-2020	12:14	#VALUE!	#VALUE!	#VALUE!
001-003	A	25-Sep-2020	1:22	0	24	39
001-003	A	25-Sep-2020	3:24	0	58	4
001-003	A	1-Oct-2020	7:11	1	29	34
001-003	A	1-Oct-2020	8:43	0	27	50
001-003	A	16-Dec-2020	15:15	0	15	25
001-003	A	16-Dec-2020	15:46	0	16	15
001-003	A	16-Dec-2020	16:12	1	0	39
001-003	A	16-Dec-2020	17:25	2	40	23
001-003	A	16-Dec-2020	20:07	0	34	58
001-003	A	16-Dec-2020	20:54	0	38	28
001-003	A	16-Dec-2020	21:39	0	48	53
001-003	A	18-Dec-2020	5:30	0	9	22
001-003	A	18-Dec-2020	5:44	1	32	43
001-003	A	18-Dec-2020	7:22	0	46	25
001-003	A	18-Dec-2020	8:19	0	19	32
001-003	A	18-Dec-2020	8:40	0	8	44
001-003	A	18-Dec-2020	9:07	2	44	29
001-003	A	18-Dec-2020	11:55	2	20	1
001-003	A	18-Dec-2020	14:17	0	11	47
001-003	A	18-Dec-2020	14:39	1	2	21
001-003	A	18-Dec-2020	15:43	0	6	41
001-003	A	18-Dec-2020	15:50	0	20	39
001-003	A	18-Dec-2020	16:12	0	24	28
001-003	A	18-Dec-2020	16:38	0	28	31
001-003	A	18-Dec-2020	17:09	3	6	1

 

[dancer5]

Couldn't figure this one out, so I've created helper columns in my main dataset for combined date/time, +23hr from episode, -23hr from episode and then used SUMIFS to calculate the Total Time within 26 hrs for each entry. I was hoping to avoid this and just have a separate sheet that filters but this way will do the job.

duration temp.xlsx
	A	B	C	D	E	F	G	H	I	J	K
4	ID	Type	Episode Date dd-mmm-yyyy	Episode time hh:mm	hh	mm	ss	combined datetime	Total within 26 hours	26	-26
5	001-001	A	16-Aug-2019	15:28	0	9	19	8/16/19 15:28	2.2	8/17/19 17:28	8/15/19 13:28
6	001-001	A	16-Aug-2019	15:56	0	10	8	8/16/19 15:56	2.2	8/17/19 17:56	8/15/19 13:56
7	001-001	A	16-Aug-2019	16:08	0	28	48	8/16/19 16:08	2.2	8/17/19 18:08	8/15/19 14:08
8	001-001	A	16-Aug-2019	16:46	0	44	8	8/16/19 16:46	2.2	8/17/19 18:46	8/15/19 14:46
9	001-001	A	16-Aug-2019	18:38	0	14	40	8/16/19 18:38	2.2	8/17/19 20:38	8/15/19 16:38
10	001-001	A	16-Aug-2019	20:00	0	21	53	8/16/19 20:00	2.2	8/17/19 22:00	8/15/19 18:00
11	001-001	A	16-Aug-2019	21:44	0	6	26	8/16/19 21:44	2.2	8/17/19 23:44	8/15/19 19:44
12	001-001	A	20-Aug-2019	13:26	0	51	4	8/20/19 13:26	5.9	8/21/19 15:26	8/19/19 11:26
13	001-001	A	20-Aug-2019	14:34	0	19	31	8/20/19 14:34	5.9	8/21/19 16:34	8/19/19 12:34
14	001-001	A	20-Aug-2019	15:18	1	9	25	8/20/19 15:18	5.9	8/21/19 17:18	8/19/19 13:18
15	001-001	A	20-Aug-2019	16:39	0	19	47	8/20/19 16:39	6.0	8/21/19 18:39	8/19/19 14:39
16	001-001	A	20-Aug-2019	17:40	0	28	11	8/20/19 17:40	6.4	8/21/19 19:40	8/19/19 15:40
17	001-001	A	20-Aug-2019	18:13	1	4	37	8/20/19 18:13	7.0	8/21/19 20:13	8/19/19 16:13
18	001-001	A	20-Aug-2019	20:20	0	21	43	8/20/19 20:20	7.1	8/21/19 22:20	8/19/19 18:20
19	001-001	A	20-Aug-2019	21:49	0	6	8	8/20/19 21:49	7.1	8/21/19 23:49	8/19/19 19:49
20	001-001	A	20-Aug-2019	21:56	0	19	23	8/20/19 21:56	7.1	8/21/19 23:56	8/19/19 19:56
21	001-001	A	20-Aug-2019	23:33	0	11	24	8/20/19 23:33	7.2	8/22/19 1:33	8/19/19 21:33
22	001-001	A	20-Aug-2019	23:50	0	9	34	8/20/19 23:50	7.2	8/22/19 1:50	8/19/19 21:50
23	001-001	A	21-Aug-2019	11:41	0	11	7	8/21/19 11:41	7.2	8/22/19 13:41	8/20/19 9:41
all sites

Cell Formulas
Range	Formula
C5:D23	C5	=N5
E5:E23	E5	=IF(X5=0, 0, V5)
F5:F23	F5	=IF(X5=0, V5, W5)
G5:G23	G5	=IF(X5=0, W5, X5)
H5:H23	H5	=[@[Episode Date dd-mmm-yyyy]]+[@[Episode time hh:mm]]
I5:I23	I5	=SUMIFS($E$5:$E$30000, $E$5:$E$30000, "<>#VALUE!", $A$5:$A$30000, A5, $H$5:$H$30000, "<="&J5, $H$5:$H$30000, ">="&K5)+((SUMIFS($F$5:$F$30000, $F$5:$F$30000, "<>#VALUE!", $A$5:$A$30000, A5, $H$5:$H$30000, "<="&J5, $H$5:$H$30000, ">="&K5))/60)
J5:J23	J5	=[@[combined datetime]]+(26/24)
K5:K23	K5	=[@[combined datetime]]-(26/24)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B4	Cell Value	=Yes	text	NO