Finding rows less than 12 minutes

[sobeitjedi]

Hi.

Here is a sample data export from a car parking system. There are three car parks, Oasis, Winter & Premier, and I would like to know who is parking for less than 12 minutes (the purpose of this is that I only want to charge those people parking longer, not those just dropping off items).

ParkingSheet1

Can you help?

 

[Sufiyan97]

You're welcome..

 

[Fluff]

How about

Parking.csv
	A	B	C	D	E				I	J	K
1	Date and Time	Event	Card number	User name	Component
2	15/03/2021 08:43	Access granted	75:76704	Sue Jones	Oasis Street Barrier IN					Sue Jones	540
3	15/03/2021 17:37	Access granted	75:76704	Sue Jones	Oasis Street Barrier OUT					Bob Edwards
4	16/03/2021 08:42	Access granted	75:76704	Sue Jones	Oasis Street Barrier IN					Chris Jameson	379
5	16/03/2021 17:12	Access granted	75:76704	Sue Jones	Oasis Street Barrier OUT					Jeff Byrne
6	17/03/2021 08:46	Access granted	75:76704	Sue Jones	Oasis Street Barrier IN					Clive Roberts	69
7	17/03/2021 17:22	Access granted	75:76704	Sue Jones	Oasis Street Barrier OUT					Hannah Frank	184
8	19/03/2021 08:46	Access granted	75:76704	Sue Jones	Oasis Street Barrier IN					Homer Williams	498
9	19/03/2021 17:46	Access granted	75:76704	Sue Jones	Oasis Street Barrier OUT					Sara Burns	67
10	22/03/2021 08:45	Access granted	75:76704	Sue Jones	Oasis Street Barrier IN					John Jones	37
11	17/03/2021 08:47	Access granted	75:76755	Bob Edwards	Winter Barrier IN					Simon Clark	482
12	17/03/2021 08:55	Access granted	75:76755	Bob Edwards	Winter Barrier OUT					Andrew Time	474
13	18/03/2021 08:39	Access granted	75:76770	Chris Jameson	Premier St Barrier IN					Steve Keith	109
14	18/03/2021 14:58	Access granted	75:76770	Chris Jameson	Premier St Barrier OUT					Mohamed Ross	507
15	22/03/2021 09:16	Access granted	75:76776	Jeff Byrne	Winter Barrier IN					Kelly Lennon	456
16	18/03/2021 15:16	Access granted	75:76747	Clive Roberts	Premier St Barrier IN					Russell Carlos	72
17	18/03/2021 16:25	Access granted	75:76747	Clive Roberts	Premier St Barrier OUT					Angela Gould	500
18	17/03/2021 09:17	Access granted	75:76751	Hannah Frank	Oasis Street Barrier IN					Julia Marge	444
19	17/03/2021 12:21	Access granted	75:76751	Hannah Frank	Oasis Street Barrier OUT					Lisa Russell	240
20	17/03/2021 08:41	Access granted	75:76765	Homer Williams	Oasis Street Barrier IN					Rachel Holly	489
21	17/03/2021 16:43	Access granted	75:76765	Homer Williams	Oasis Street Barrier OUT					William Hand	75
22	18/03/2021 09:08	Access granted	75:76765	Homer Williams	Oasis Street Barrier IN					Kevin Kiln	478
23	18/03/2021 17:26	Access granted	75:76765	Homer Williams	Oasis Street Barrier OUT					Kelly Anders	296
24	19/03/2021 09:01	Access granted	75:76765	Homer Williams	Oasis Street Barrier IN					Greg Moyles	452
25	19/03/2021 14:32	Access granted	75:76765	Homer Williams	Oasis Street Barrier OUT					Charles Simpson	357
26	22/03/2021 08:59	Access granted	75:76765	Homer Williams	Oasis Street Barrier IN					Sue Downs
27	17/03/2021 14:43	Access granted	7C:79405	Sara Burns	Premier St Barrier IN					Henry Thomas	429
28	17/03/2021 15:50	Access granted	7C:79405	Sara Burns	Premier St Barrier OUT					Paul Starr	456
29	17/03/2021 16:27	Access granted	7C:79454	John Jones	Winter Barrier OUT
30	22/03/2021 08:42	Access granted	7C:79454	John Jones	Winter Barrier IN
31	16/03/2021 08:48	Access granted	7C:79456	Simon Clark	Premier St Barrier IN
32	16/03/2021 16:37	Access granted	7C:79456	Simon Clark	Premier St Barrier OUT
33	17/03/2021 08:42	Access granted	7C:79456	Simon Clark	Premier St Barrier IN
34	17/03/2021 16:33	Access granted	7C:79456	Simon Clark	Premier St Barrier OUT
35	18/03/2021 08:50	Access granted	7C:79456	Simon Clark	Premier St Barrier IN
36	18/03/2021 16:30	Access granted	7C:79456	Simon Clark	Premier St Barrier OUT
37	19/03/2021 08:37	Access granted	7C:79456	Simon Clark	Premier St Barrier IN
38	19/03/2021 16:39	Access granted	7C:79456	Simon Clark	Premier St Barrier OUT
39	15/03/2021 08:17	Access granted	7C:79476	Andrew Time	Premier St Barrier IN
40	15/03/2021 16:11	Access granted	7C:79476	Andrew Time	Premier St Barrier OUT
Parking

Cell Formulas
Range	Formula
J2:J28	J2	=UNIQUE(D2:D133)
K2:K28	K2	=LET(m,MAX(FILTER($A$3:$A$133-$A$2:$A$132,($D$3:$D$133=J2)*(RIGHT($E$3:$E$133,3)="out"),0))*1440,IF(m>12,m,""))
Dynamic array formulas.

 

[sobeitjedi]

How about

Parking.csv
	A	B	C	D	E				I	J	K
1	Date and Time	Event	Card number	User name	Component
2	15/03/2021 08:43	Access granted	75:76704	Sue Jones	Oasis Street Barrier IN					Sue Jones	540
3	15/03/2021 17:37	Access granted	75:76704	Sue Jones	Oasis Street Barrier OUT					Bob Edwards
4	16/03/2021 08:42	Access granted	75:76704	Sue Jones	Oasis Street Barrier IN					Chris Jameson	379
5	16/03/2021 17:12	Access granted	75:76704	Sue Jones	Oasis Street Barrier OUT					Jeff Byrne
6	17/03/2021 08:46	Access granted	75:76704	Sue Jones	Oasis Street Barrier IN					Clive Roberts	69
7	17/03/2021 17:22	Access granted	75:76704	Sue Jones	Oasis Street Barrier OUT					Hannah Frank	184
8	19/03/2021 08:46	Access granted	75:76704	Sue Jones	Oasis Street Barrier IN					Homer Williams	498
9	19/03/2021 17:46	Access granted	75:76704	Sue Jones	Oasis Street Barrier OUT					Sara Burns	67
10	22/03/2021 08:45	Access granted	75:76704	Sue Jones	Oasis Street Barrier IN					John Jones	37
11	17/03/2021 08:47	Access granted	75:76755	Bob Edwards	Winter Barrier IN					Simon Clark	482
12	17/03/2021 08:55	Access granted	75:76755	Bob Edwards	Winter Barrier OUT					Andrew Time	474
13	18/03/2021 08:39	Access granted	75:76770	Chris Jameson	Premier St Barrier IN					Steve Keith	109
14	18/03/2021 14:58	Access granted	75:76770	Chris Jameson	Premier St Barrier OUT					Mohamed Ross	507
15	22/03/2021 09:16	Access granted	75:76776	Jeff Byrne	Winter Barrier IN					Kelly Lennon	456
16	18/03/2021 15:16	Access granted	75:76747	Clive Roberts	Premier St Barrier IN					Russell Carlos	72
17	18/03/2021 16:25	Access granted	75:76747	Clive Roberts	Premier St Barrier OUT					Angela Gould	500
18	17/03/2021 09:17	Access granted	75:76751	Hannah Frank	Oasis Street Barrier IN					Julia Marge	444
19	17/03/2021 12:21	Access granted	75:76751	Hannah Frank	Oasis Street Barrier OUT					Lisa Russell	240
20	17/03/2021 08:41	Access granted	75:76765	Homer Williams	Oasis Street Barrier IN					Rachel Holly	489
21	17/03/2021 16:43	Access granted	75:76765	Homer Williams	Oasis Street Barrier OUT					William Hand	75
22	18/03/2021 09:08	Access granted	75:76765	Homer Williams	Oasis Street Barrier IN					Kevin Kiln	478
23	18/03/2021 17:26	Access granted	75:76765	Homer Williams	Oasis Street Barrier OUT					Kelly Anders	296
24	19/03/2021 09:01	Access granted	75:76765	Homer Williams	Oasis Street Barrier IN					Greg Moyles	452
25	19/03/2021 14:32	Access granted	75:76765	Homer Williams	Oasis Street Barrier OUT					Charles Simpson	357
26	22/03/2021 08:59	Access granted	75:76765	Homer Williams	Oasis Street Barrier IN					Sue Downs
27	17/03/2021 14:43	Access granted	7C:79405	Sara Burns	Premier St Barrier IN					Henry Thomas	429
28	17/03/2021 15:50	Access granted	7C:79405	Sara Burns	Premier St Barrier OUT					Paul Starr	456
29	17/03/2021 16:27	Access granted	7C:79454	John Jones	Winter Barrier OUT
30	22/03/2021 08:42	Access granted	7C:79454	John Jones	Winter Barrier IN
31	16/03/2021 08:48	Access granted	7C:79456	Simon Clark	Premier St Barrier IN
32	16/03/2021 16:37	Access granted	7C:79456	Simon Clark	Premier St Barrier OUT
33	17/03/2021 08:42	Access granted	7C:79456	Simon Clark	Premier St Barrier IN
34	17/03/2021 16:33	Access granted	7C:79456	Simon Clark	Premier St Barrier OUT
35	18/03/2021 08:50	Access granted	7C:79456	Simon Clark	Premier St Barrier IN
36	18/03/2021 16:30	Access granted	7C:79456	Simon Clark	Premier St Barrier OUT
37	19/03/2021 08:37	Access granted	7C:79456	Simon Clark	Premier St Barrier IN
38	19/03/2021 16:39	Access granted	7C:79456	Simon Clark	Premier St Barrier OUT
39	15/03/2021 08:17	Access granted	7C:79476	Andrew Time	Premier St Barrier IN
40	15/03/2021 16:11	Access granted	7C:79476	Andrew Time	Premier St Barrier OUT
Parking

Cell Formulas
Range	Formula
J2:J28	J2	=UNIQUE(D2:D133)
K2:K28	K2	=LET(m,MAX(FILTER($A$3:$A$133-$A$2:$A$132,($D$3:$D$133=J2)*(RIGHT($E$3:$E$133,3)="out"),0))*1440,IF(m>12,m,""))
Dynamic array formulas.

I'm lost now. Think you may have been closer with your original answer.
I don't know what the number in K represents? Using Sue Jones as an example, she parked on 5 unique days, which means I need to charge her 5 times. Why does it say 540?

 

[Fluff]

Missed the bit about per day.
Try

=IF(AND(RIGHT(E2,3)="out",C2=C1),IF((A2-A1)*1440=AGGREGATE(14,6,($A$3:$A$133-$A$2:$A$132)*1440/($C$3:$C$133=C2)/(RIGHT($E$3:$E$133,3)="out")/(INT($A$3:$A$133)=INT($A2)),1),"Charge",""),"")

 

[Fluff]

Ignore the previous formula, try

=IF(AND(RIGHT(E2,3)="out",C2=C1,(A2-N(A1))*1440>12),IF((A2-A1)=MAX(FILTER($A$3:$A$133-$A$2:$A$132,($D$3:$D$133=D2)*(RIGHT($E$3:$E$133,3)="out")*(INT($A$3:$A$133)=INT(A2)),0)),"Charge",""),"")

 

[sobeitjedi]

Ignore the previous formula, try

=IF(AND(RIGHT(E2,3)="out",C2=C1,(A2-N(A1))*1440>12),IF((A2-A1)=MAX(FILTER($A$3:$A$133-$A$2:$A$132,($D$3:$D$133=D2)*(RIGHT($E$3:$E$133,3)="out")*(INT($A$3:$A$133)=INT(A2)),0)),"Charge",""),"")

Getting "that function isn't valid" error?

 

[Fluff]

Both build 2002 & 2102 of 365 should have the filter function.
if you type =fil into a cell is Filter one of the options in the drop down?

 

[sobeitjedi]

Both build 2002 & 2102 of 365 should have the filter function.
if you type =fil into a cell is Filter one of the options in the drop down?

The option was there in 2102 but not in 2002, so got it working in the later version and it looks very good! Thank you! ... One more thing to throw into the mix ... The report varies in length each time it's received. Your formula works if there are 133 rows, but what if the data is less or more? Maybe as long as data exists in column A, then count the number of rows?

 

[Fluff]

To account for more rows you can use

=IF(AND(RIGHT(E2,3)="out",C2=C1,(A2-N(A1))*1440>12),IF((A2-A1)=MAX(FILTER($A$3:$A$1001-$A$2:$A$1000,($D$3:$D$1001=D2)*(RIGHT($E$3:$E$1001,3)="out")*(INT($A$3:$A$1001)=INT(A2)),0)),"Charge",""),"")

or if you need it to work on both systems

=IF(AND(RIGHT(E2,3)="out",C2=C1,(A2-N(A1))*1440>12),IF((A2-A1)=AGGREGATE(14,6,($A$3:$A$1001-$A$2:$A$1000)/($C$3:$C$1001=C2)/(RIGHT($E$3:$E$1001,3)="out")/(INT($A$3:$A$1001)=INT($A2)),1),"Charge",""),"")

 

[sobeitjedi]

To account for more rows you can use

=IF(AND(RIGHT(E2,3)="out",C2=C1,(A2-N(A1))*1440>12),IF((A2-A1)=MAX(FILTER($A$3:$A$1001-$A$2:$A$1000,($D$3:$D$1001=D2)*(RIGHT($E$3:$E$1001,3)="out")*(INT($A$3:$A$1001)=INT(A2)),0)),"Charge",""),"")

or if you need it to work on both systems

=IF(AND(RIGHT(E2,3)="out",C2=C1,(A2-N(A1))*1440>12),IF((A2-A1)=AGGREGATE(14,6,($A$3:$A$1001-$A$2:$A$1000)/($C$3:$C$1001=C2)/(RIGHT($E$3:$E$1001,3)="out")/(INT($A$3:$A$1001)=INT($A2)),1),"Charge",""),"")

Wow. Amazing. I'll do some testing tomorrow to check it all works on various reports but doing a quick test now, I'm so impressed. Cannot thank you enough.