Calculate Time Difference Per Day Based On Criteria

[PS_Richard]

Hi all,

I'm hoping someone might be able to give me a hand here in working out a way to get this data out. I've tried with MINIFS and MAXIFS setting to a month time frame but this only picks up the first instance and the last instance within the month where I need to work it out per day.

Here is an export of data that I need some help with.

Example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Site ID	Site Name	Equipment ID	Serial Number	Pool ID	Pool Name	Event Time	Event Time Local	Event Start Time Local	Charger ID	Event Code	Duration	EventVPC	Event Type	Available
2	1234	Warehouse A	4321	123789	9999	BAL	03/10/2022 04:52	03/10/2022 05:52	02/10/2022 19:13	6	Pick	639.5	2.18		1
3	1234	Warehouse A	4321	123789	9999	BAL	03/10/2022 05:00	03/10/2022 06:00	03/10/2022 05:42	1	Pick	18.4	02/01/1900 04:48		0
4	1234	Warehouse A	4321	123789	9999	BAL	03/10/2022 05:01	03/10/2022 06:01	03/10/2022 05:52	6	Battery connect	9.3	1.93	High Voltaqe	0
5	1234	Warehouse A	4321	123789	9999	BAL	03/10/2022 05:02	03/10/2022 06:02	03/10/2022 06:01	6	Charge Started	0.3	2.01		0
6	1234	Warehouse A	4321	123789	9999	BAL	03/10/2022 05:46	03/10/2022 06:46	03/10/2022 01:17	8	Battery connect	329.3	1.93	High Voltaqe	0
7	1234	Warehouse A	4321	123789	9999	BAL	03/10/2022 05:46	03/10/2022 06:46	03/10/2022 06:46	8	Charge Started	0.3	2.01		0
8	1234	Warehouse A	4321	123789	9999	BAL	03/10/2022 05:50	03/10/2022 06:50	03/10/2022 06:00	1	Battery connect	50.1	1.96	High Voltaqe	0
9	1234	Warehouse A	4321	123789	9999	BAL	03/10/2022 05:51	03/10/2022 06:51	03/10/2022 06:50	1	Charge Started	0.8	2.04		0
10	1234	Warehouse A	4321	123789	9999	BAL	03/10/2022 05:53	03/10/2022 06:53	02/10/2022 19:33	9	On-charge mispick during maintenance	679.4	2.57		0
11	1234	Warehouse A	4321	123789	9999	BAL	03/10/2022 05:53	03/10/2022 06:53	02/10/2022 15:58	2	Quarantine Exit	895.3	2.1	Battery Selected	0
12	1234	Warehouse A	4321	123789	9999	BAL	03/10/2022 05:53	03/10/2022 06:53	02/10/2022 23:30	4	On-charge mispick during maintenance	443.7	1.96		0
13	1234	Warehouse A	4321	123789	9999	BAL	03/10/2022 05:55	03/10/2022 06:55	03/10/2022 06:53	2	Battery connect	1.8	2.1	False Pick	0
14	1234	Warehouse A	4321	123789	9999	BAL	03/10/2022 05:55	03/10/2022 06:55	03/10/2022 06:53	4	Battery connect	1.8	1.96	False Pick	0
15	1234	Warehouse A	4321	123789	9999	BAL	03/10/2022 05:56	03/10/2022 06:56	03/10/2022 06:55	2	Charge Started	0.8	2.19		0
16	1234	Warehouse A	4321	123789	9999	BAL	03/10/2022 05:56	03/10/2022 06:56	03/10/2022 06:55	4	Charge Started	0.7	2.04		0
17	1234	Warehouse A	4321	123789	9999	BAL	03/10/2022 06:21	03/10/2022 07:21	02/10/2022 23:23	7	Gassing voltage	478.7	1.97		0
18	1234	Warehouse A	4321	123789	9999	BAL	03/10/2022 06:41	03/10/2022 07:41	02/10/2022 21:29	5	Charge Complete	612	2.24		1
19	1234	Warehouse A	4321	123789	9999	BAL	03/10/2022 06:51	03/10/2022 07:51	03/10/2022 07:51		Status update	0	0		1
20	1234	Warehouse A	4321	123789	9999	BAL	03/10/2022 06:56	03/10/2022 07:56	03/10/2022 06:56	2	Gassing voltage	60.7	2.1		1
21	1234	Warehouse A	4321	123789	9999	BAL	03/10/2022 08:52	03/10/2022 09:52	03/10/2022 01:17	10	Gassing voltage	515	1.95		1
22	1234	Warehouse A	4321	123789	9999	BAL	03/10/2022 08:57	03/10/2022 09:57	03/10/2022 05:17	3	Gassing voltage	279.3	1.98		1
23	1234	Warehouse A	4321	123789	9999	BAL	03/10/2022 09:56	03/10/2022 10:56	02/10/2022 23:23	7	Charge Complete	693	2.23		2
24	1234	Warehouse A	4321	987321	9999	VNA	27/10/2022 02:03	27/10/2022 03:03	26/10/2022 23:49	11	Pick	193.4	2.17		0
25	1234	Warehouse A	4321	987321	9999	VNA	27/10/2022 02:04	27/10/2022 03:04	27/10/2022 00:17	6	Battery connect	166.7	1.95	High Voltaqe	0
26	1234	Warehouse A	4321	987321	9999	VNA	27/10/2022 02:04	27/10/2022 03:04	26/10/2022 18:25	5	Battery connect	518.9	1.96	High Voltaqe	0
27	1234	Warehouse A	4321	987321	9999	VNA	27/10/2022 02:04	27/10/2022 03:04	27/10/2022 03:04	6	Charge Started	0.3	2.02		0
28	1234	Warehouse A	4321	987321	9999	VNA	27/10/2022 02:05	27/10/2022 03:05	27/10/2022 03:04	5	Charge Started	0.7	2.04		0
29	1234	Warehouse A	4321	987321	9999	VNA	27/10/2022 02:12	27/10/2022 03:12	26/10/2022 19:36	7	Gassing voltage	455.7	1.97		0
30	1234	Warehouse A	4321	987321	9999	VNA	27/10/2022 02:50	27/10/2022 03:50	26/10/2022 18:30	3	Charge Complete	559.2	2.24		1
31	1234	Warehouse A	4321	987321	9999	VNA	27/10/2022 18:03	27/10/2022 19:03	27/10/2022 08:30	1	Pick	193.4	2.17		0
32	1234	Warehouse A	4321	987321	9999	VNA	27/10/2022 18:10	27/10/2022 19:10	27/10/2022 10:56	2	Battery connect	166.7	1.95	High Voltaqe	0
33	1234	Warehouse A	4321	987321	9999	VNA	27/10/2022 18:18	27/10/2022 19:18	27/10/2022 18:42	13	Battery connect	518.9	1.96	High Voltaqe	0
34	1234	Warehouse A	4321	987321	9999	VNA	27/10/2022 18:19	27/10/2022 19:19	27/10/2022 19:18	2	Charge Started	0.3	2.02		0
35	1234	Warehouse A	4321	987321	9999	VNA	27/10/2022 18:31	27/10/2022 19:31	27/10/2022 14:18	13	Charge Started	0.7	2.04		0
36	1234	Warehouse A	4321	987321	9999	VNA	27/10/2022 20:44	27/10/2022 21:44	27/10/2022 11:32	4	Gassing voltage	455.7	1.97		0
37	1234	Warehouse A	4321	987321	9999	VNA	27/10/2022 21:05	27/10/2022 22:05	27/10/2022 11:50	9	Charge Complete	559.2	2.24		1
Sheet1

Based on this, as soon as a 0 (zero) appears in column O (Oscar) I need it to look at column H for the date/time the event started and also split this out by whichever Pool Name (column F) the battery is. For the first instance of Here it is 03/10/2022 06:00:27 and then on that day when column O is not a 0 happens at 03/10/2022 07:41:59 so a time difference of 1 hour 41 minutes and 32 seconds. This can happen numerous times within the day and other the month.

In the example above the next instance starts 27/10/2022 03:03:14 and ends at 27/10/2022 03:50:01 but then another occasion that day is at 27/10/2022 19:03:33 to 27/10/2022 21:44:03. Respectively this is 46 minutes and 47 seconds then 2 hours 40 minutes and 30 seconds giving a total for that day of 3 hours 27 minutes and 17 seconds.

These figures I then want to appear into a table like this.

Example.xlsx
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ
2	Date	01/10/2022	02/10/2022	03/10/2022	04/10/2022	05/10/2022	06/10/2022	07/10/2022	08/10/2022	09/10/2022	10/10/2022	11/10/2022	12/10/2022	13/10/2022	14/10/2022	15/10/2022	16/10/2022	17/10/2022	18/10/2022	19/10/2022	20/10/2022	21/10/2022	22/10/2022	23/10/2022	24/10/2022	25/10/2022	26/10/2022	27/10/2022	28/10/2022	29/10/2022	30/10/2022	31/10/2022
3	BAL			102
4	VNA																											208
Sheet2

With the duration round up to the next full minute. This is to then allow me to present in either a bar / line graph the data for the month broken down by day. I'd rather this be worked out using a formula as such as I'm not great/used Power Query and currently that training is on the back burner.

Thanks in advance for any help anyone can give.

 

[Anthony47]

A possible solution:
-in sheet2 you create the date sequence by using in F1 the formula

=SEQUENCE(,MAX(Sheet1!H:H)-MIN(Sheet1!H:H)+5,INT(MIN(Sheet1!H:H))-2)

-now create the list of Pools by using in Sheet2 E2 the formula

=UNIQUE(Sheet1!F2:F1000)

Now you can calculate the allocated time per day /per pool using in Sheet2!F2 the formula

=IFERROR(LET(ETL,Sheet1!$H$2:$H$37,cDay,F$1,cPool,$E2,Pool,Sheet1!$F$2:$F$37,AvailA,Sheet1!$O$2:$O$37,AvailB,OFFSET(AvailA,-1,0,ROWS(AvailA)+2,1),Start,IF((Pool=cPool)*(INT(ETL)=cDay)*(AvailA=0)*(AvailB>0),ETL,""),Stop,IF((Pool=cPool)*(INT(ETL)=cDay)*(AvailA>0)*(AvailB=0),ETL,""),CIP,SUM(AGGREGATE(15,6,Stop,SEQUENCE(COUNT(Stop)))-AGGREGATE(15,6,Start,SEQUENCE(COUNT(Stop)))),CIP)*1440,0)

Copy to the right for as many dates are listed, then copy this line of formulas down for as many Pools are listed

Beware that the value I calculated for Oct 27 is 3h49min, ie 229 minutes

If you need to customize the formula:

ETL,Sheet1!$H$2:$H$37    This is the EventTimeLocal Column in Sheet1
cDay,F$1                 This is the Day under evaluation
cPool,$E2                This is the Pool under evaluation
Pool,Sheet1!$F$2:$F$37   This is the Pool Column in Sheet1
AvailA,Sheet1!$O$2:$O$37 This is the Available Column in Sheet1

Try...

Cartel1
	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		01-ott-22	02-ott-22	03-ott-22	04-ott-22	05-ott-22	06-ott-22	07-ott-22	08-ott-22	09-ott-22	10-ott-22	11-ott-22	12-ott-22
2	BAL	-	-	101	-	-	-	-	-	-	-	-	-
3	VNA	-	-	-	-	-	-	-	-	-	-	-	-
4	0
5
6
7
Sheet2

Cell Formulas
Range	Formula
F1:AH1	F1	=SEQUENCE(,MAX(Sheet1!H:H)-MIN(Sheet1!H:H)+5,INT(MIN(Sheet1!H:H))-2)
E2:E4	E2	=UNIQUE(Sheet1!F2:F1000)
F2:Q3	F2	=IFERROR(LET(ETL,Sheet1!$H$2:$H$37,cDay,F$1,cPool,$E2,Pool,Sheet1!$F$2:$F$37,AvailA,Sheet1!$O$2:$O$37,AvailB,OFFSET(AvailA,-1,0,ROWS(AvailA)+2,1),Start,IF((Pool=cPool)*(INT(ETL)=cDay)*(AvailA=0)*(AvailB>0),ETL,""),Stop,IF((Pool=cPool)*(INT(ETL)=cDay)*(AvailA>0)*(AvailB=0),ETL,""),CIP,SUM(AGGREGATE(15,6,Stop,SEQUENCE(COUNT(Stop)))-AGGREGATE(15,6,Start,SEQUENCE(COUNT(Stop)))),CIP)*1440,0)
Dynamic array formulas.

 

[PS_Richard]

Thanks @Anthony47 I've given that a whirl and at first glance its doing something but when I pick a few days to sanity check manually things aren't adding up right. I've got some data below to show what I mean.

This first section is some raw data (omitted sensitive details) and limited the data to a 'transaction' as such where the calculation should pick up due to the Available in column O has changed from a positive number to 0.

Raw Data

Example Sheet.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Site Id	Site Name	Equipment Id	Serial Number	Pool Id	Pool Name	Event Time	Event Time Local	Event Start Time Local	Charger Id	Event Code	Duration	EventVPC	Event Type	Available	Available Cooled	Charging	Con Not Charging	No Battery	Quarantine	Unknown	Source Data File	dbId
2	1234	Warehouse A	8027	45070531	35150	C BAL	18/11/2021 03:05	18/11/2021 03:05	17/11/2021 19:18	9	Gassing voltage	467	1.97		3	1	5	0	3	0	0	I45070531-3220416-1.csv	393391811
3	1234	Warehouse A	8027	45070531	35150	C BAL	18/11/2021 10:16	18/11/2021 10:16	18/11/2021 10:16		Status update	0	0		5	2	3	0	3	0	0	I45070531-3221616-1.csv	393490606
4	1234	Warehouse A	8027	45070531	35150	C BAL	18/11/2021 10:51	18/11/2021 10:51	18/11/2021 01:24	1	Charge Complete	567	2.21		5	1	3	0	3	0	0	I45070531-3221616-1.csv	393490615
5	1234	Warehouse A	8027	45070531	35150	C BAL	18/11/2021 17:06	18/11/2021 17:06	18/11/2021 11:54	4	Gassing voltage	311.4	1.94		1	1	6	0	4	0	0	I45070531-3222216-1.csv	393541933
6	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 00:22	18/11/2021 00:22	17/11/2021 17:41	9	Pick	401	2.15		5	2	3	0	10	0	0	I45070531-3220416-1.csv	393391799
7	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 00:29	18/11/2021 00:29	17/11/2021 20:55	10	Gassing voltage	213.6	2.03		5	2	3	0	10	0	0	I45070531-3220416-1.csv	393391800
8	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 00:32	18/11/2021 00:32	17/11/2021 22:22	2	Battery connect	129.8	2.01	High Voltaqe	5	2	3	1	9	0	0	I45070531-3220416-1.csv	393391801
9	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 00:33	18/11/2021 00:33	18/11/2021 00:32	2	Charge Started	1.3	2.1		5	2	4	0	9	0	0	I45070531-3220416-1.csv	393391802
10	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 01:06	18/11/2021 01:06	17/11/2021 19:47	4	Gassing voltage	319.3	2.01		5	4	4	0	9	0	0	I45070531-3220416-1.csv	393391803
11	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 02:59	18/11/2021 02:59	18/11/2021 00:22	9	Battery connect	156.9	2.04	High Voltaqe	5	5	4	1	8	0	0	I45070531-3220416-1.csv	393391808
12	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 02:59	18/11/2021 02:59	17/11/2021 17:58	3	Pick	541.4	2.14		4	4	4	1	9	0	0	I45070531-3220416-1.csv	393391809
13	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 03:00	18/11/2021 03:00	18/11/2021 02:59	9	Charge Started	1.4	2.12		4	4	5	0	9	0	0	I45070531-3220416-1.csv	393391810
14	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 03:05	18/11/2021 03:05	17/11/2021 18:05	6	Pick	540.1	2.15		3	3	5	0	10	0	0	I45070531-3220416-1.csv	393391812
15	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 03:05	18/11/2021 03:05	18/11/2021 02:59	3	Battery connect	5.7	2.14	False Pick	3	3	5	1	9	0	0	I45070531-3220416-1.csv	393391813
16	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 03:06	18/11/2021 03:06	18/11/2021 03:05	3	Charge Started	1.2	2.28		3	3	6	0	9	0	0	I45070531-3220416-1.csv	393391814
17	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 03:08	18/11/2021 03:08	18/11/2021 03:06	3	Gassing voltage	2.1	2.14		3	3	6	0	9	0	0	I45070531-3220416-1.csv	393391815
18	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 03:18	18/11/2021 03:18	17/11/2021 20:55	10	Charge Complete	382.6	2.21		4	3	5	0	9	0	0	I45070531-3220416-1.csv	393391817
19	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 03:18	18/11/2021 03:18	18/11/2021 03:05	6	Battery connect	12.9	2	High Voltaqe	4	3	5	1	8	0	0	I45070531-3220416-1.csv	393391818
20	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 03:18	18/11/2021 03:18	17/11/2021 22:26	7	On-charge mispick	291.6	1.72		4	3	4	1	9	0	0	I45070531-3220416-1.csv	393391819
21	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 03:19	18/11/2021 03:19	18/11/2021 03:18	6	Charge Started	1.5	2.08		4	3	5	0	9	0	0	I45070531-3220416-1.csv	393391820
22	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 04:13	18/11/2021 04:13	17/11/2021 19:47	4	Charge Complete	505.7	2.23		5	3	4	0	9	0	0	I45070531-3220416-1.csv	393391822
23	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 04:47	18/11/2021 04:47	18/11/2021 03:06	3	Charge Complete	100.8	2.19		6	3	3	0	9	0	0	I45070531-3221016-1.csv	393439215
24	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 05:06	18/11/2021 05:06	18/11/2021 03:18	7	Battery connect	108.3	2.02	High Voltaqe	6	3	3	1	8	0	0	I45070531-3221016-1.csv	393439216
25	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 05:06	18/11/2021 05:06	17/11/2021 18:45	8	Pick	621.3	2.15		5	2	3	1	9	0	0	I45070531-3221016-1.csv	393439217
26	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 05:08	18/11/2021 05:08	18/11/2021 05:06	7	Charge Started	1.3	2.1		5	2	4	0	9	0	0	I45070531-3221016-1.csv	393439218
27	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 05:38	18/11/2021 05:38	18/11/2021 05:06	8	Battery connect	31.7	2.03	High Voltaqe	5	2	4	1	8	0	0	I45070531-3221016-1.csv	393439219
28	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 05:39	18/11/2021 05:39	18/11/2021 05:38	8	Charge Started	0.8	2.1		5	2	5	0	8	0	0	I45070531-3221016-1.csv	393439220
29	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 05:39	18/11/2021 05:39	17/11/2021 18:59	1	Pick	639.4	2.14		4	1	5	0	9	0	0	I45070531-3221016-1.csv	393439221
30	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 05:43	18/11/2021 05:43	18/11/2021 00:33	2	Gassing voltage	310.3	2.01		4	1	5	0	9	0	0	I45070531-3221016-1.csv	393439222
31	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 05:44	18/11/2021 05:44	17/11/2021 19:13	5	Pick	631.1	2.15		3	0	5	0	10	0	0	I45070531-3221016-1.csv	393439223
32	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 05:51	18/11/2021 05:51	18/11/2021 05:39	1	Battery connect	12.6	2.01	High Voltaqe	3	0	5	1	9	0	0	I45070531-3221016-1.csv	393439224
33	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 05:53	18/11/2021 05:53	18/11/2021 05:51	1	Charge Started	1.3	2.09		3	0	6	0	9	0	0	I45070531-3221016-1.csv	393439225
34	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 06:26	18/11/2021 06:26	18/11/2021 03:18	10	Pick	188.9	2.19		2	0	6	0	10	0	0	I45070531-3221016-1.csv	393439227
35	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 06:29	18/11/2021 06:29	18/11/2021 05:44	5	Battery connect	45.8	2	High Voltaqe	2	0	6	1	9	0	0	I45070531-3221016-1.csv	393439228
36	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 06:31	18/11/2021 06:31	18/11/2021 06:29	5	Charge Started	1.3	2.08		2	0	7	0	9	0	0	I45070531-3221016-1.csv	393439229
37	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 07:14	18/11/2021 07:14	18/11/2021 04:13	4	Pick	181.4	2.2		1	0	7	0	10	0	0	I45070531-3221016-1.csv	393439236
38	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 07:15	18/11/2021 07:15	18/11/2021 03:00	9	Gassing voltage	254.9	2.04		1	0	7	0	10	0	0	I45070531-3221016-1.csv	393439237
39	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 07:24	18/11/2021 07:24	18/11/2021 07:14	4	Battery connect	9.8	2.05	High Voltaqe	1	0	7	1	9	0	0	I45070531-3221016-1.csv	393439238
40	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 07:25	18/11/2021 07:25	18/11/2021 07:24	4	Charge Started	1	2.12		1	0	8	0	9	0	0	I45070531-3221016-1.csv	393439239
41	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 08:24	18/11/2021 08:24	18/11/2021 04:47	3	Pick	217.4	2.14		0	0	8	0	10	0	0	I45070531-3221016-1.csv	393439240
42	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 08:28	18/11/2021 08:28	18/11/2021 06:26	10	Battery connect	121.3	2.02	High Voltaqe	0	0	8	1	9	0	0	I45070531-3221016-1.csv	393439241
43	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 08:29	18/11/2021 08:29	18/11/2021 08:28	10	Charge Started	1.4	2.11		0	0	9	0	9	0	0	I45070531-3221016-1.csv	393439242
44	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 08:32	18/11/2021 08:32	18/11/2021 03:19	6	Gassing voltage	312.5	2		0	0	9	0	9	0	0	I45070531-3221016-1.csv	393439243
45	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 08:55	18/11/2021 08:55	18/11/2021 00:33	2	Charge Complete	502.1	2.23		1	0	8	0	9	0	0	I45070531-3221016-1.csv	393439244
46	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 10:09	18/11/2021 10:09	18/11/2021 07:25	4	Gassing voltage	164.3	2.05		1	0	8	0	9	0	0	I45070531-3221016-1.csv	393439249
47	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 10:15	18/11/2021 10:15	18/11/2021 03:00	9	Charge Complete	434.9	2.23		2	0	7	0	9	0	0	I45070531-3221016-1.csv	393439250
48	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 10:16	18/11/2021 10:16	18/11/2021 10:16		Status update	0	0		2	0	7	0	9	0	0	I45070531-3221616-1.csv	393490607
49	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 10:29	18/11/2021 10:29	18/11/2021 05:08	7	Gassing voltage	321.1	2.02		2	0	7	0	9	0	0	I45070531-3221616-1.csv	393490608
50	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 10:50	18/11/2021 10:50	18/11/2021 08:24	3	Battery connect	145.8	2.01	High Voltaqe	2	0	7	1	8	0	0	I45070531-3221616-1.csv	393490612
51	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 10:50	18/11/2021 10:50	18/11/2021 08:55	2	Pick	115.4	2.2		1	0	7	1	9	0	0	I45070531-3221616-1.csv	393490613
52	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 10:51	18/11/2021 10:51	18/11/2021 05:53	1	Gassing voltage	297.8	2.01		1	0	7	1	9	0	0	I45070531-3221616-1.csv	393490614
53	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 10:52	18/11/2021 10:52	18/11/2021 10:50	3	Charge Started	1.6	2.09		1	0	8	0	9	0	0	I45070531-3221616-1.csv	393490616
54	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 11:20	18/11/2021 11:20	18/11/2021 03:19	6	Charge Complete	480.4	2.2		2	0	7	0	9	0	0	I45070531-3221616-1.csv	393490617
55	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 11:23	18/11/2021 11:23	18/11/2021 10:15	9	Pick	67.2	2.2		1	0	7	0	10	0	0	I45070531-3221616-1.csv	393490618
56	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 12:00	18/11/2021 12:00	18/11/2021 06:31	5	Gassing voltage	329.6	2		1	0	7	0	10	0	0	I45070531-3221616-1.csv	393490622
57	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 12:16	18/11/2021 12:16	18/11/2021 10:50	2	Battery connect	85.7	2.01	High Voltaqe	1	0	7	1	9	0	0	I45070531-3221616-1.csv	393490623
58	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 12:18	18/11/2021 12:18	18/11/2021 12:16	2	Charge Started	1.6	2.09		1	0	8	0	9	0	0	I45070531-3221616-1.csv	393490624
59	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 12:33	18/11/2021 12:33	18/11/2021 05:39	8	Gassing voltage	414.5	2.03		1	0	8	0	9	0	0	I45070531-3221616-1.csv	393490625
60	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 13:33	18/11/2021 13:33	18/11/2021 08:29	10	Gassing voltage	303.5	2.02		1	0	8	0	9	0	0	I45070531-3221616-1.csv	393490626
61	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 13:57	18/11/2021 13:57	18/11/2021 05:08	7	Charge Complete	529.1	2.21		2	0	7	0	9	0	0	I45070531-3221616-1.csv	393490627
62	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 14:00	18/11/2021 14:00	18/11/2021 05:53	1	Charge Complete	487	2.21		3	0	6	0	9	0	0	I45070531-3221616-1.csv	393490628
63	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 14:17	18/11/2021 14:17	18/11/2021 07:25	4	Charge Complete	412.4	2.23		4	0	5	0	9	0	0	I45070531-3221616-1.csv	393490630
64	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 15:11	18/11/2021 15:11	18/11/2021 10:52	3	Gassing voltage	259.6	2.01		4	0	5	0	9	0	0	I45070531-3221616-1.csv	393490631
65	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 15:27	18/11/2021 15:27	18/11/2021 11:22	9	Battery connect	244.2	2	High Voltaqe	4	0	5	1	8	0	0	I45070531-3221616-1.csv	393490632
66	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 15:27	18/11/2021 15:27	18/11/2021 11:20	6	Pick	247.6	2.15		3	0	5	1	9	0	0	I45070531-3221616-1.csv	393490633
67	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 15:28	18/11/2021 15:28	18/11/2021 15:27	9	Charge Started	1.3	2.08		3	0	6	0	9	0	0	I45070531-3221616-1.csv	393490634
68	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 15:30	18/11/2021 15:30	18/11/2021 15:27	6	Battery connect	2.7	2.15	False Pick	3	0	6	1	8	0	0	I45070531-3221616-1.csv	393490635
69	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 15:31	18/11/2021 15:31	18/11/2021 13:57	7	Pick	94	2.19		2	0	6	1	9	0	0	I45070531-3221616-1.csv	393490636
70	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 15:31	18/11/2021 15:31	18/11/2021 15:30	6	Charge Started	1.2	2.29		2	0	7	0	9	0	0	I45070531-3221616-1.csv	393490637
71	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 15:33	18/11/2021 15:33	18/11/2021 15:31	6	Gassing voltage	2	2.15		2	0	7	0	9	0	0	I45070531-3221616-1.csv	393490638
72	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 15:36	18/11/2021 15:36	18/11/2021 05:39	8	Charge Complete	597.4	2.23		3	0	6	0	9	0	0	I45070531-3221616-1.csv	393490639
73	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 15:41	18/11/2021 15:41	18/11/2021 06:31	5	Charge Complete	550.4	2.22		4	0	5	0	9	0	0	I45070531-3221616-1.csv	393490640
74	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 15:50	18/11/2021 15:50	18/11/2021 14:00	1	Pick	110.2	2.18		3	0	5	0	10	0	0	I45070531-3221616-1.csv	393490645
75	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 16:00	18/11/2021 16:00	18/11/2021 15:31	7	Battery connect	29.7	2	High Voltaqe	3	0	5	1	9	0	0	I45070531-3221616-1.csv	393490646
76	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 16:02	18/11/2021 16:02	18/11/2021 16:00	7	Charge Started	1.6	2.08		3	0	6	0	9	0	0	I45070531-3221616-1.csv	393490647
77	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 16:04	18/11/2021 16:04	18/11/2021 14:17	4	Pick	106.6	2.2		2	0	6	0	10	0	0	I45070531-3221616-1.csv	393490648
78	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 16:08	18/11/2021 16:08	18/11/2021 15:50	1	Battery connect	18.2	2	High Voltaqe	2	0	6	1	9	0	0	I45070531-3221616-1.csv	393490649
79	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 16:10	18/11/2021 16:10	18/11/2021 16:08	1	Charge Started	1.6	2.08		2	0	7	0	9	0	0	I45070531-3221616-1.csv	393490650
80	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 16:12	18/11/2021 16:12	18/11/2021 15:36	8	Pick	35.7	2.2		1	0	7	0	10	0	0	I45070531-3221616-1.csv	393490651
81	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 16:12	18/11/2021 16:12	18/11/2021 16:04	4	Battery connect	8	2.02	High Voltaqe	1	0	7	1	9	0	0	I45070531-3221616-1.csv	393490652
82	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 16:14	18/11/2021 16:14	18/11/2021 16:12	4	Charge Started	1.6	2.1		1	0	8	0	9	0	0	I45070531-3221616-1.csv	393490653
83	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 16:22	18/11/2021 16:22	18/11/2021 08:29	10	Charge Complete	472.7	2.21		2	0	7	0	9	0	0	I45070531-3222216-1.csv	393541931
84	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 17:06	18/11/2021 17:06	18/11/2021 12:18	2	Gassing voltage	288.4	2.01		2	0	7	0	9	0	0	I45070531-3222216-1.csv	393541934
85	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 17:12	18/11/2021 17:12	18/11/2021 15:31	6	Charge Complete	101	2.21		3	0	6	0	9	0	0	I45070531-3222216-1.csv	393541935
86	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 17:57	18/11/2021 17:57	18/11/2021 10:52	3	Charge Complete	425	2.19		4	0	5	0	9	0	0	I45070531-3222216-1.csv	393541942
87	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 19:03	18/11/2021 19:03	18/11/2021 15:41	5	Pick	201.9	2.19		3	0	5	0	10	0	0	I45070531-3222216-1.csv	393541946
88	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 19:03	18/11/2021 19:03	18/11/2021 16:12	8	Battery connect	171.3	2.01	High Voltaqe	3	0	5	1	9	0	0	I45070531-3222216-1.csv	393541947
89	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 19:04	18/11/2021 19:04	18/11/2021 19:03	8	Charge Started	0.9	2.08		3	0	6	0	9	0	0	I45070531-3222216-1.csv	393541948
90	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 19:18	18/11/2021 19:18	18/11/2021 19:03	5	Battery connect	14.9	2.05	High Voltaqe	3	0	6	1	8	0	0	I45070531-3222216-1.csv	393541950
91	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 19:18	18/11/2021 19:18	18/11/2021 16:22	10	Pick	176.6	2.18		2	0	6	1	9	0	0	I45070531-3222216-1.csv	393541951
92	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 19:19	18/11/2021 19:19	18/11/2021 19:18	5	Charge Started	1.3	2.13		2	0	7	0	9	0	0	I45070531-3222216-1.csv	393541952
93	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 19:53	18/11/2021 19:53	18/11/2021 12:18	2	Charge Complete	455.6	2.19		3	0	6	0	9	0	0	I45070531-3222216-1.csv	393541954
94	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 20:15	18/11/2021 20:15	18/11/2021 16:14	4	Gassing voltage	241.1	2.02		3	0	6	0	9	0	0	I45070531-3222216-1.csv	393541955
95	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 20:28	18/11/2021 20:28	18/11/2021 16:02	7	Gassing voltage	266.4	2		3	0	6	0	9	0	0	I45070531-3222216-1.csv	393541957
96	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 20:36	18/11/2021 20:36	18/11/2021 15:28	9	Gassing voltage	308.2	2		3	0	6	0	9	0	0	I45070531-3222216-1.csv	393541958
97	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 20:46	18/11/2021 20:46	18/11/2021 16:10	1	Gassing voltage	276.5	2		3	0	6	0	9	0	0	I45070531-3222216-1.csv	393541959
98	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 22:42	18/11/2021 22:42	18/11/2021 19:19	5	Gassing voltage	203.4	2.05		3	0	6	0	9	0	0	I45070531-3230416-1.csv	393592748
99	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 23:00	18/11/2021 23:00	18/11/2021 16:14	4	Charge Complete	406.7	2.19		4	0	5	0	9	0	0	I45070531-3230416-1.csv	393592751
100	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 23:14	18/11/2021 23:14	18/11/2021 16:02	7	Charge Complete	431.9	2.18		5	1	4	0	9	0	0	I45070531-3230416-1.csv	393592752
101	1234	Warehouse A	8027	45070531	35151	VNA	18/11/2021 23:34	18/11/2021 23:34	18/11/2021 16:10	1	Charge Complete	444.1	2.18		6	1	3	0	9	0	0	I45070531-3230416-1.csv	393592753
102	1234	Warehouse A	8027	45070531	35150	C BAL	17/07/2022 18:10	17/07/2022 19:10	17/07/2022 19:01	10	Battery connect	9.1	1.94	High Voltaqe	1	0	4	1	2	3	0	I45070531-1981851-1.csv	433317672
103	1234	Warehouse A	8027	45070531	35150	C BAL	17/07/2022 18:11	17/07/2022 19:11	17/07/2022 19:10	10	Charge Started	0.3	2.01		1	0	5	0	2	3	0	I45070531-1981851-1.csv	433317673
104	1234	Warehouse A	8027	45070531	35150	C BAL	17/07/2022 20:38	17/07/2022 21:38	17/07/2022 16:48	7	Pick	289.4	2.2		0	0	5	0	3	3	0	I45070531-1990051-1.csv	433342110
105	1234	Warehouse A	8027	45070531	35150	C BAL	17/07/2022 20:40	17/07/2022 21:40	17/07/2022 19:08	6	Battery connect	151.8	1.92	High Voltaqe	0	0	5	1	2	3	0	I45070531-1990051-1.csv	433342111
106	1234	Warehouse A	8027	45070531	35150	C BAL	17/07/2022 20:40	17/07/2022 21:40	17/07/2022 21:40	6	Charge Started	0.3	2		0	0	6	0	2	3	0	I45070531-1990051-1.csv	433342112
107	1234	Warehouse A	8027	45070531	35150	C BAL	17/07/2022 22:13	17/07/2022 23:13	17/07/2022 17:55	5	Gassing voltage	317.4	1.95		0	0	6	0	2	3	0	I45070531-1990051-1.csv	433342116
108	1234	Warehouse A	8027	45070531	35150	C BAL	17/07/2022 22:59	17/07/2022 23:59	17/07/2022 15:29	9	Gassing voltage	510.5	1.98		0	0	6	0	2	3	0	I45070531-1990051-1.csv	433342117
109	1234	Warehouse A	8027	45070531	35150	C BAL	17/07/2022 23:37	18/07/2022 00:37	17/07/2022 21:38	7	Battery connect	179.3	1.95	High Voltaqe	0	0	6	1	1	3	0	I45070531-1990051-1.csv	433342122
110	1234	Warehouse A	8027	45070531	35150	C BAL	17/07/2022 23:37	18/07/2022 00:37	18/07/2022 00:37	7	Charge Started	0.3	2.03		0	0	7	0	1	3	0	I45070531-1990051-1.csv	433342123
111	1234	Warehouse A	8027	45070531	35150	C BAL	17/07/2022 23:49	18/07/2022 00:49	17/07/2022 17:55	5	Zero Available Pick	413.8	3.23		0	0	6	0	2	3	0	I45070531-1990051-1.csv	433342124
112	1234	Warehouse A	8027	45070531	35150	C BAL	18/07/2022 01:27	18/07/2022 02:27	17/07/2022 15:29	9	Charge Complete	658.8	2.23		1	0	5	0	2	3	0	I45070531-1990651-1.csv	433367642
113	1234	Warehouse A	8027	45070531	35150	C BAL	18/07/2022 01:42	18/07/2022 02:42	17/07/2022 18:32	2	Gassing voltage	490.4	1.93		1	0	5	0	2	3	0	I45070531-1990651-1.csv	433367643
Sheet1

I've been able to get all the formulas into sheet 2 to work in the fashion you have given. Only difference for this test is that I manually inputted the dates at the top row for ease of checking.

Example Sheet.xlsx
	A	B	C	D	E
1	Pool / Date	18/11/2021	16/08/2022	17/08/2022	18/08/2022
2	C BAL	0	0	0	0
3	VNA	30.583333	0	0	0
4
5
6
7		08:24:56		21:38:05
8		08:28:13		21:40:35
9		08:29:34		21:40:50
10		08:32:14		23:13:19
11				23:59:35
12				00:37:22
13				00:37:39
14				00:49:43
15
16		00:07:18		#########
17
18				03:11:38
19		00:08:00		03:12:00
20		8		192
21
22	Answer should be 8			Answer should be 192 in C BAL
Sheet2

Cell Formulas
Range	Formula
A2:A3	A2	=UNIQUE(Sheet1!F2:F101)
B2:E2,C3:E3	B2	=IFERROR(LET(ETL,Sheet1!$H$2:$H$112,cDay,B$1,cPool,$A2,Pool,Sheet1!$F$2:$F$101,AvailA,Sheet1!$O$2:$O$112,AvailB,OFFSET(AvailA,-1,0,ROWS(AvailA)+2,1),Start,IF((Pool=cPool)*(INT(ETL)=cDay)*(AvailA=0)*(AvailB>0),ETL,""),Stop,IF((Pool=cPool)*(INT(ETL)=cDay)*(AvailA>0)*(AvailB=0),ETL,""),CIP,SUM(AGGREGATE(15,6,Stop,SEQUENCE(COUNT(Stop)))-AGGREGATE(15,6,Start,SEQUENCE(COUNT(Stop)))),CIP)*1440,0)
B3	B3	=IFERROR(LET(ETL,Sheet1!$H$2:$H$101,cDay,B$1,cPool,$A3,Pool,Sheet1!$F$2:$F$101,AvailA,Sheet1!$O$2:$O$101,AvailB,OFFSET(AvailA,-1,0,ROWS(AvailA)+2,1),Start,IF((Pool=cPool)*(INT(ETL)=cDay)*(AvailA=0)*(AvailB>0),ETL,""),Stop,IF((Pool=cPool)*(INT(ETL)=cDay)*(AvailA>0)*(AvailB=0),ETL,""),CIP,SUM(AGGREGATE(15,6,Stop,SEQUENCE(COUNT(Stop)))-AGGREGATE(15,6,Start,SEQUENCE(COUNT(Stop)))),CIP)*1440,0)
B16	B16	=B10-B7
D16	D16	=D14-D7
Dynamic array formulas.

As shown above its getting data for the time duration on the 18-11-2021 and marking it as 30.58 yet when manually checking I have it at 8 minutes. Likewise on the other date I've picked which is the 17-08-2022 which could be an issue due to it going from one day to another and I know Excel can be funny when it comes to time being taken away and causing a negative (as seen in cell D16) so below I have shown manually what it should look like to then see how many minutes it could be.

My original data set is over 40,000 rows long, so one of the ideas I had was to remove all instances when column O is not a zero to see if the formulas would work then but alas it doesn't pick up anything as I guess this is due to it looking for a non zero result.

Again, I'm grateful for the help you've been able to give so far so hoping that by giving some more information here it might help either yourself or someone else?

Thanks

 

[Anthony47]

While I disagree (or I misunderstood the question) about the result for 18-Nov-2021 (it is 31 minutes), I agree that Start & Stop at midnight is a problem, with that formula

I therefore decided to switch to a User Defined Function that corresponds to the following code:

Function PSR(ByRef BigTbl As Range, ByRef PooList As Range, ByRef DtList As Range, Optional ByVal DBG As Boolean = False) As Variant
Dim bgArr, etlArr, plArr, avArr
Dim oArr(), I As Long, J As Long, K As Long
Dim cPool As String, cDay As Date, fvStop As Date, fvStart As Date
'
etlArr = BigTbl.Cells(1, 8).Resize(BigTbl.Rows.Count, 1).Value      'Event Local Time
plArr = BigTbl.Cells(1, 6).Resize(BigTbl.Rows.Count, 1).Value       'Pool name
avArr = BigTbl.Cells(1, 15).Resize(BigTbl.Rows.Count + 2, 1).Value  'Available
ReDim oArr(1 To PooList.Rows.Count, 1 To DtList.Columns.Count)
'
For I = 1 To DtList.Columns.Count
    cDay = DtList.Cells(1, I)
    For J = 1 To PooList.Rows.Count
        cPool = PooList.Cells(J, 1)
        For K = 2 To UBound(etlArr)
            If Int(etlArr(K, 1)) = cDay And plArr(K, 1) = cPool And avArr(K, 1) = 0 And avArr(K - 1, 1) > 0 Then
                fvStart = etlArr(K, 1)          'Start time detected
            End If
            If Int(etlArr(K, 1)) = cDay And plArr(K, 1) = cPool And avArr(K, 1) > 0 And avArr(K - 1, 1) = 0 Then
                fvStop = etlArr(K, 1)
            End If
            If fvStart > 0 And fvStop > 0 Then
                If DBG Then
                    oArr(J, I) = oArr(J, I) & fvStart & "--" & fvStop & " / "
                Else
                    oArr(J, I) = oArr(J, I) + fvStop - fvStart
                End If
                fvStart = 0: fvStop = 0
            End If
        Next K
        If fvStart > 0 And fvStop = 0 Then      'Start but no Stop: till 24:00
            If DBG Then
                oArr(J, I) = oArr(J, I) & fvStart & "--" & "To 24:00" & " / "
            Else
                oArr(J, I) = oArr(J, I) - fvStart + Int(fvStart + 1)
            End If
            fvStart = 0: fvStop = 0
        End If
        If fvStart = 0 And fvStop > 0 Then      'Stop but no Start: from 0:00
            If DBG Then
                oArr(J, I) = oArr(J, I) & "From 0:00" & "--" & fvStop & " / "
            Else
                oArr(J, I) = oArr(J, I) + fvStop - Int(fvStop)
            End If
            fvStart = 0: fvStop = 0
        End If
    Next J
Next I
If DBG = False Then
    For I = 1 To UBound(oArr)
        For J = 1 To UBound(oArr, 2)
            oArr(I, J) = oArr(I, J) * 1440
        Next J
    Next I
End If
PSR = oArr
End Function

Insert this code into a Standard Module of your vba project; then return to Excel.
At this point you have available the function PSR, with the following syntax

PSR(AddressOfTheBigTable, AddressOfThePoolList, AddressForTheDaysList [optional Debug, default is False])

Having created the list of dates in Sheet2, F1 to IR1
Having created the list of Pools in Sheet2, E2:E3
The formula I set in F2 is

=PSR(Sheet1!A1:O200,E2:E3,F1:IR1)

This returns all the results; namely I got
18-Nov-2021 , VNA: 31 minutes (this come from starting 18/11/2021 08:24:00 and ending 18/11/2021 08:55:00)
17-Jul-2022 , C BAL: 142 min (this comes from starting at 17/07/2022 21:38:00 and ending at 24:00)
18-Jul-2022 , C BAL: 147 min (this comes from starting at 18/07/2022 at 0:00 and ending at 2:27)

The function PST can be invoked with a "debug" parametre: if you use in F2

=PSR(Sheet1!A1:O200,E2:E3,F1:IR1,TRUE)

then you will get the info on how the result is calculated; you need to enlarge the columns to see the information
For 17 and 18-Jul-2022 I got:
17th: 17/07/2022 21:38:00--To 24:00 /
18th: From 0:00--18/07/2022 02:27:00 /

Try...

 

[PS_Richard]

Apologies @Anthony47, you are right with the timings/durations. The VBA script works perfecting and doesn't cause my work laptop to freeze! Thank you so much for doing this for me as it'll save me hours trying to do it manually.

 

[Anthony47]

Thank you for the feedback