FIRST and the LAST time readings from the daily card readings and ignoring the ones in between

[pulsarkuant]

We are using the attached excel to log Staff START and end times. We have logs from the card readers and basically copying the raw logs to the "Raw Data" sheet and hitting the CommadnButton to distribute to the staff listed in the "Personel" sheet. It works fine, but I want to only pick the very FIRST and the LAST reading during each day and ignore any readings in between. How can I tell the below code to pick the first and the last reading?

##The command button on the "Raw Data" sheet has the below VB code and attached an image.

Private Sub CommandButton1_Click()

Dim irow As Long
 
 Application.ScreenUpdating = False
 With ActiveSheet
    For irow = .Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
        If UCase(.Cells(irow, 2)) Like "*ADMINDOOR*" Or _
            UCase(.Cells(irow, 2)) Like "*LIFT*" Or _
            UCase(.Cells(irow, 2)) Like "*CANTEEN*" Or _
             UCase(.Cells(irow, 2)) Like "*GATE*" Then
            .Rows(irow).Delete
        End If
    Next
 End With
 Application.ScreenUpdating = True

 
For a = 3 To Sheets("Personel").Cells(65000, 1).End(xlUp).Row
For b = 1 To Sheets("Raw data").Cells(65000, 1).End(xlUp).Row
 c = Len(Sheets("Personel").Cells(a, 1))
 If Left(Cells(b, 2), c) = Sheets("Personel").Cells(a, 1) Then
 d = Day(Cells(b, 1)) * 2
 If Hour(Cells(b, 1)) > 11 Then d = d + 1
Sheets("Personel").Cells(a, d + 1) = Hour(Cells(b, 1)) & ":" & Minute(Cells(b, 1))
End If
Next
Next

End Sub

##Minisheet shows the raw data to process

FEBRUARY_2022 Staff Attendance Report.xlsm
	A	B
2	17/02/2022 6:43	John Brown Card Access at <R41:Rdr03> into B7Door [Card 546888]
3	17/02/2022 6:43	IB L3 Staff RoomB Locked by (Door Logic) (D042)
4	17/02/2022 6:43	IB L3 Staff RoomB Timed Unlocked for 00 h 00 min 05 s by R25: AdminBuilding L2 Comms Room (Door Logic) (D042)
5	17/02/2022 6:43	Elizabeth Scott Card Access at <R25:Rdr01> into IB L3 Staff RoomB [Card 546599]
6	17/02/2022 6:42	Elizabeth Scott Card Access at <R32:Rdr01> into AdminBuilding SW B [Card 546599]
7	17/02/2022 6:42	IB L1 S Auto Door + PIN Locked by (Door Logic) (D016)
8	17/02/2022 6:42	IB L1 S Auto Door + PIN Timed Unlocked for 00 h 00 min 05 s by R12: AdminBuilding L2 Comms Room (Door Logic) (D016)
9	17/02/2022 6:42	Elizabeth Scott Card Access at <R12:Rdr01> into IB L1 S Auto Door + PIN [Card 546599]
10	17/02/2022 6:40	E1 Timed Unlocked for 00 h 00 min 05 s by R46: E Block (Door Logic) (D073)
11	17/02/2022 6:40	Abraham Parry Card Access at <R46:Rdr03> into E1Door [Card 658215]
12	17/02/2022 13:43	John Brown Card Access at <R41:Rdr03> into B7Door [Card 546888]
13	17/02/2022 14:44	Elizabeth Scott Card Access at <R12:Rdr01> into IB L1 S Auto Door + PIN [Card 546599]
14	17/02/2022 14:45	Abraham Parry Card Access at <R46:Rdr03> into E1Door [Card 658215]
15	17/02/2022 16:43	John Brown Card Access at <R41:Rdr03> into B7Door [Card 546888]
16	17/02/2022 16:44	Elizabeth Scott Card Access at <R12:Rdr01> into IB L1 S Auto Door + PIN [Card 546599]
17	17/02/2022 16:45	Abraham Parry Card Access at <R46:Rdr03> into E1Door [Card 658215]
18	15/02/2022 7:55	John Brown Card Access at <R41:Rdr03> into B7Door [Card 546888]
19	15/02/2022 7:55	IB L3 Staff RoomB Locked by (Door Logic) (D042)
20	15/02/2022 7:55	IB L3 Staff RoomB Timed Unlocked for 00 h 00 min 05 s by R25: AdminBuilding L2 Comms Room (Door Logic) (D042)
21	15/02/2022 7:55	Elizabeth Scott Card Access at <R25:Rdr01> into IB L3 Staff RoomB [Card 546599]
22	15/02/2022 11:40	Elizabeth Scott Card Access at <R32:Rdr01> into AdminBuilding SW B [Card 546599]
23	15/02/2022 11:40	IB L1 S Auto Door + PIN Locked by (Door Logic) (D016)
24	15/02/2022 11:40	IB L1 S Auto Door + PIN Timed Unlocked for 00 h 00 min 05 s by R12: AdminBuilding L2 Comms Room (Door Logic) (D016)
25	15/02/2022 11:40	Elizabeth Scott Card Access at <R12:Rdr01> into IB L1 S Auto Door + PIN [Card 546599]
26	15/02/2022 11:40	E1 Timed Unlocked for 00 h 00 min 05 s by R46: E Block (Door Logic) (D073)
27	15/02/2022 11:40	Abraham Parry Card Access at <R46:Rdr03> into E1Door [Card 658215]
28	15/02/2022 11:40	John Brown Card Access at <R41:Rdr03> into B7Door [Card 546888]
29	15/02/2022 16:12	Elizabeth Scott Card Access at <R12:Rdr01> into IBr + PIN [Card 546599]
30	15/02/2022 16:11	Abraham Parry Card Access at <R46:Rdr03> into E1Door [Card 658215]
31	15/02/2022 16:11	John Brown Card Access at <R41:Rdr03> into B7Door [Card 546888]
32	15/02/2022 16:11	Elizabeth Scott Card Access at <R12:Rdr01> into IB L1 S Auto Door + PIN [Card 546599]
33	15/02/2022 16:11	Abraham Parry Card Access at <R46:Rdr03> into E1Door [Card 658215]
Raw data

##The below shows the end result in the second sheet called "Personel"

FEBRUARY_2022 Staff Attendance Report.xlsm
	A	B	C	D	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	AK	AL
1			TUE		WED		THU		FRI		SAT		SUN		MON		TUE		WED		THU		FRI		SAT		SUN		MON		TUE		WED		THU		FRI
2			1	1	2	2	3	3	4	4	5	5	6	6	7	7	8	8	9	9	10	10	11	11	12	12	13	13	14	14	15	15	16	16	17	17	18	18
3	John Brown																														11:40	16:11			6:43	16:43
4	Elizabeth Scott																														11:40	16:11			6:42	16:44
5	Abraham Parry																														11:40	16:11			6:40	16:45
6
7
Personel

 

[BSALV]

you're welcome

 

[BSALV]

suppose Bill Gates is working in your firm, then you get in problems with those "forbidden" words, like "gate".
Work around, check for those words after the 15th character with
b = (InStr(1, a(i, 2), strg, vbTextCompare) > 15)

          For i = UBound(a) To 2 Step -1                        'loop from last row upwards
               For Each strg In Array("admindoor", "lift", "Canteen", "gate")     'list of specific term not to be used
                    b = (InStr(1, a(i, 2), strg, vbTextCompare) > 15)    'is that term in that cell = skip that row, don't look in the 1st 15 characters
                    If b Then Exit For
               Next