Picking the FIRST and the LAST time readings from the daily card readings

P

pulsarkuant

New Member
Joined
Jan 9, 2011
Messages
20
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 reading in between. How can I tell the below code to pick the first and the last reading?

Command button on the "Raw Data" sheet has the bleow VB code and attached an image.

VBA Code: 
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
 

Attachments

  • Explaination.jpg
    Explaination.jpg
    169.8 KB · Views: 7

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
you can do it without VBA, just with one table and a pivottable
Can you add an example (10 lines) with the XL2BB-tool ?
 
Upvote 0
Sure, please find the minisheet.



FEBRUARY_2022 Staff Attendance Report.xlsm
AB
1Local TimeText
217/02/2022 6:43John Brown Card Access at <R41:Rdr03> into B7Door [Card 546888]
317/02/2022 6:43IB L3 Staff RoomB Locked by (Door Logic) (D042)
417/02/2022 6:43IB L3 Staff RoomB Timed Unlocked for 00 h 00 min 05 s by R25: AdminBuilding L2 Comms Room (Door Logic) (D042)
517/02/2022 6:43Elizabeth Scott Card Access at <R25:Rdr01> into IB L3 Staff RoomB [Card 546599]
617/02/2022 6:42Elizabeth Scott Card Access at <R32:Rdr01> into AdminBuilding SW B [Card 546599]
717/02/2022 6:42IB L1 S Auto Door + PIN Locked by (Door Logic) (D016)
817/02/2022 6:42IB L1 S Auto Door + PIN Timed Unlocked for 00 h 00 min 05 s by R12: AdminBuilding L2 Comms Room (Door Logic) (D016)
917/02/2022 6:42Elizabeth Scott Card Access at <R12:Rdr01> into IB L1 S Auto Door + PIN [Card 546599]
1017/02/2022 6:40E1 Timed Unlocked for 00 h 00 min 05 s by R46: E Block (Door Logic) (D073)
1117/02/2022 6:40Abraham Parry Card Access at <R46:Rdr03> into E1Door [Card 658215]
1217/02/2022 13:43John Brown Card Access at <R41:Rdr03> into B7Door [Card 546888]
1317/02/2022 14:44Elizabeth Scott Card Access at <R12:Rdr01> into IB L1 S Auto Door + PIN [Card 546599]
1417/02/2022 14:45Abraham Parry Card Access at <R46:Rdr03> into E1Door [Card 658215]
1517/02/2022 16:43John Brown Card Access at <R41:Rdr03> into B7Door [Card 546888]
1617/02/2022 16:44Elizabeth Scott Card Access at <R12:Rdr01> into IB L1 S Auto Door + PIN [Card 546599]
1717/02/2022 16:45Abraham Parry Card Access at <R46:Rdr03> into E1Door [Card 658215]
1815/02/2022 7:55John Brown Card Access at <R41:Rdr03> into B7Door [Card 546888]
1915/02/2022 7:55IB L3 Staff RoomB Locked by (Door Logic) (D042)
2015/02/2022 7:55IB L3 Staff RoomB Timed Unlocked for 00 h 00 min 05 s by R25: AdminBuilding L2 Comms Room (Door Logic) (D042)
2115/02/2022 7:55Elizabeth Scott Card Access at <R25:Rdr01> into IB L3 Staff RoomB [Card 546599]
2215/02/2022 11:40Elizabeth Scott Card Access at <R32:Rdr01> into AdminBuilding SW B [Card 546599]
2315/02/2022 11:40IB L1 S Auto Door + PIN Locked by (Door Logic) (D016)
2415/02/2022 11:40IB L1 S Auto Door + PIN Timed Unlocked for 00 h 00 min 05 s by R12: AdminBuilding L2 Comms Room (Door Logic) (D016)
2515/02/2022 11:40Elizabeth Scott Card Access at <R12:Rdr01> into IB L1 S Auto Door + PIN [Card 546599]
2615/02/2022 11:40E1 Timed Unlocked for 00 h 00 min 05 s by R46: E Block (Door Logic) (D073)
2715/02/2022 11:40Abraham Parry Card Access at <R46:Rdr03> into E1Door [Card 658215]
2815/02/2022 11:40John Brown Card Access at <R41:Rdr03> into B7Door [Card 546888]
2915/02/2022 16:12Elizabeth Scott Card Access at <R12:Rdr01> into IBr + PIN [Card 546599]
3015/02/2022 16:11Abraham Parry Card Access at <R46:Rdr03> into E1Door [Card 658215]
3115/02/2022 16:11John Brown Card Access at <R41:Rdr03> into B7Door [Card 546888]
3215/02/2022 16:11Elizabeth Scott Card Access at <R12:Rdr01> into IB L1 S Auto Door + PIN [Card 546599]
3315/02/2022 16:11Abraham Parry Card Access at <R46:Rdr03> into E1Door [Card 658215]
Raw data
 
Upvote 0
like this
stront voeren 210217.xlsb
ABCDEFGHIJKLM
1Local TimeTextCardpersonDagenWaarden
217/02/2022 6:43John Brown Card Access at <R41:Rdr03> into B7Door [Card 546888]546888John Brown15/feb17/feb
317/02/2022 6:43IB L3 Staff RoomB Locked by (Door Logic) (D042)--CardpersonArrivalDeparture#ArrivalDeparture#
417/02/2022 6:43IB L3 Staff RoomB Timed Unlocked for 00 h 00 min 05 s by R25: AdminBuilding L2 Comms Room (Door Logic) (D042)----7:5511:4056:406:435
517/02/2022 6:43Elizabeth Scott Card Access at <R25:Rdr01> into IB L3 Staff RoomB [Card 546599]546599Elizabeth Scott546599Elizabeth Scott7:5516:1256:4216:445
617/02/2022 6:42Elizabeth Scott Card Access at <R32:Rdr01> into AdminBuilding SW B [Card 546599]546599Elizabeth Scott546888John Brown7:5516:1136:4316:433
717/02/2022 6:42IB L1 S Auto Door + PIN Locked by (Door Logic) (D016)--658215Abraham Parry11:4016:1136:4016:453
817/02/2022 6:42IB L1 S Auto Door + PIN Timed Unlocked for 00 h 00 min 05 s by R12: AdminBuilding L2 Comms Room (Door Logic) (D016)--
917/02/2022 6:42Elizabeth Scott Card Access at <R12:Rdr01> into IB L1 S Auto Door + PIN [Card 546599]546599Elizabeth Scott
1017/02/2022 6:40E1 Timed Unlocked for 00 h 00 min 05 s by R46: E Block (Door Logic) (D073)--
1117/02/2022 6:40Abraham Parry Card Access at <R46:Rdr03> into E1Door [Card 658215]658215Abraham Parry
1217/02/2022 13:43John Brown Card Access at <R41:Rdr03> into B7Door [Card 546888]546888John Brown
1317/02/2022 14:44Elizabeth Scott Card Access at <R12:Rdr01> into IB L1 S Auto Door + PIN [Card 546599]546599Elizabeth Scott
1417/02/2022 14:45Abraham Parry Card Access at <R46:Rdr03> into E1Door [Card 658215]658215Abraham Parry
1517/02/2022 16:43John Brown Card Access at <R41:Rdr03> into B7Door [Card 546888]546888John Brown
1617/02/2022 16:44Elizabeth Scott Card Access at <R12:Rdr01> into IB L1 S Auto Door + PIN [Card 546599]546599Elizabeth Scott
1717/02/2022 16:45Abraham Parry Card Access at <R46:Rdr03> into E1Door [Card 658215]658215Abraham Parry
1815/02/2022 7:55John Brown Card Access at <R41:Rdr03> into B7Door [Card 546888]546888John Brown
1915/02/2022 7:55IB L3 Staff RoomB Locked by (Door Logic) (D042)--
2015/02/2022 7:55IB L3 Staff RoomB Timed Unlocked for 00 h 00 min 05 s by R25: AdminBuilding L2 Comms Room (Door Logic) (D042)--
2115/02/2022 7:55Elizabeth Scott Card Access at <R25:Rdr01> into IB L3 Staff RoomB [Card 546599]546599Elizabeth Scott
2215/02/2022 11:40Elizabeth Scott Card Access at <R32:Rdr01> into AdminBuilding SW B [Card 546599]546599Elizabeth Scott
2315/02/2022 11:40IB L1 S Auto Door + PIN Locked by (Door Logic) (D016)--
2415/02/2022 11:40IB L1 S Auto Door + PIN Timed Unlocked for 00 h 00 min 05 s by R12: AdminBuilding L2 Comms Room (Door Logic) (D016)--
2515/02/2022 11:40Elizabeth Scott Card Access at <R12:Rdr01> into IB L1 S Auto Door + PIN [Card 546599]546599Elizabeth Scott
2615/02/2022 11:40E1 Timed Unlocked for 00 h 00 min 05 s by R46: E Block (Door Logic) (D073)--
2715/02/2022 11:40Abraham Parry Card Access at <R46:Rdr03> into E1Door [Card 658215]658215Abraham Parry
2815/02/2022 11:40John Brown Card Access at <R41:Rdr03> into B7Door [Card 546888]546888John Brown
2915/02/2022 16:12Elizabeth Scott Card Access at <R12:Rdr01> into IBr + PIN [Card 546599]546599Elizabeth Scott
3015/02/2022 16:11Abraham Parry Card Access at <R46:Rdr03> into E1Door [Card 658215]658215Abraham Parry
3115/02/2022 16:11John Brown Card Access at <R41:Rdr03> into B7Door [Card 546888]546888John Brown
3215/02/2022 16:11Elizabeth Scott Card Access at <R12:Rdr01> into IB L1 S Auto Door + PIN [Card 546599]546599Elizabeth Scott
3315/02/2022 16:11Abraham Parry Card Access at <R46:Rdr03> into E1Door [Card 658215]658215Abraham Parry
Blad3
Cell Formulas
RangeFormula
C2:C33C2=IFERROR(SUBSTITUTE(MID([@Text],SEARCH("[Card ",[@Text])+6,20),"]",""),"-")
D2:D33D2=IF([@Card]<>"-",LEFT([@Text],SEARCH(" Card Access",[@Text])-1),"-")
 
Last edited:
Upvote 0
Solution
Hi,

I see you use the Card number however card numbers frequently change. What I see is quite a significant change in the structure and way of using. I think I'll continue searching a simple code to tell my existing code to use the earliest and the latest time. Thank you so much for your help, I really appreciate it. I'll mark this topic as a best answer for someone possibly may use it and reopen a new one.
Kind regards
 
Upvote 0
VBA Code: 
Private Sub CommandButton1_Click()

     Dim aPersonel

     Set dict = CreateObject("scripting.dictionary")            'dictionary to store unique dates

     With Sheets("Personel")
          x = .Range("A1:A" & .Range("A" & .Rows.Count).End(xlUp).Row)     'read the personel names
     End With
     aPersonel = x                                              ' array where everything is tempory stored
     aPersonel(1, 1) = "Date"
     aPersonel(2, 1) = "A/P"

     With Sheets("Log")
          a = .Range("A1").CurrentRegion.Resize(, 2).Value2     'read the whole log in an array
          mymin = Int(Application.Min(Application.Index(a, 0, 1)))     'smallest date
          mymax = Int(Application.Max(Application.Index(a, 0, 1)))     'largest date
          kol = (mymax - mymin + 1) * 3                         'necessary columns
          ReDim Preserve aPersonel(1 To UBound(aPersonel), 1 To kol)

          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(a(i, 2), strg) > 0)              'is that term in that cell = skip that row
                    If b Then Exit For
               Next

               If Not b Then                                    'not a Canteen, gate, ...
                    For p = 3 To UBound(aPersonel)              'loop through all personel names
                         b1 = (StrComp(Left(a(i, 2), Len(aPersonel(p, 1))), aPersonel(p, 1), vbTextCompare) = 0)     'first part of your log corresponds with somebody
                         If b1 Then p1 = p: Exit For
                    Next

                    If b1 Then                                  'a known personel
                         mydate = Int(a(i, 1)): mytime = a(i, 1) - mydate     'split timestamp into date and time
                         If Not dict.exists(mydate) Then        'check if date already exists, if not
                              k = 3 * dict.Count + 2
                              dict.Add mydate, k                'add to dictionary
                              aPersonel(1, k) = mydate          'add new date in 1st row and appropriate column
                              aPersonel(2, k) = "Arrival": aPersonel(2, k + 1) = "Depature": aPersonel(2, k + 2) = "delta"     'add arrival, departure and delta in 2nd row
                         End If

                         k = dict(mydate)                       'corresponding column for mydate
                         If Len(aPersonel(p1, k)) = 0 Then aPersonel(p1, k) = mytime     'in case arrival is empty, add time
                         aPersonel(p1, k) = Application.Min(mytime, aPersonel(p1, k))     'smallest time
                         aPersonel(p1, k + 1) = Application.Max(mytime, aPersonel(p1, k + 1))     'largest time
                         aPersonel(p1, k + 2) = aPersonel(p1, k + 1) - aPersonel(p1, k)     'largest-smallest
                    End If
               End If
          Next
     End With

     With Sheets("log").Range("AA1").Resize(UBound(aPersonel), UBound(aPersonel, 2))     'write to here
          .Resize(1).NumberFormat = "dd/mm/yy"                  '1st row in date-format
          .Offset(1).NumberFormat = "hh:mm"                     'all the rest in time-format
          .Value = aPersonel                                    'write array to sheet
          .EntireColumn.AutoFit
     End With

End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Autofilter code based on username
Replies
1
Views
169
aabrazil1
A
A
  • Question
Cannot enter text in textbox on form
Replies
3
Views
130
Anthony47
A
J
  • Question
VBA to check for new sheets and add cell info from them
Replies
0
Views
242
Jessica553
J
T
  • Solved
I keep getting error message "member already exists in an object module from which this project module derives
Replies
4
Views
473
Skyybot
Skyybot
V
  • Question
VBA Excel cycle through sheets, find last row in table and autofill with data
Replies
1
Views
214
mumps
mumps

Forum statistics

Threads
1,214,784
Messages
6,121,540
Members
449,038
Latest member
Guest1337

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top