Clock system

[colaps]

Hello,

This is the data i have ( Sheet "CLOCK" ):

Badge ID	Date	Time
1022​	10/11/2021​	08:11:56​
1022​	10/11/2021​	16:32:00​
1022​	11/11/2021​	21:05:15​
1022​	12/11/2021​	06:22:37​
2560​	03/11/2021​	08:13​
2560​	10/11/2021​	10:32​
2560​	10/11/2021​	10:14​
2560​	04/11/2021​	19:08​
2560​	05/11/2021​	08:22​
2560​	05/11/2021​	18:53​
2560​	08/11/2021​	08:24​
2560​	08/11/2021​	18:20​
2560​	09/11/2021​	08:25​
2560​	09/11/2021​	20:48​

This is the output i need ( Sheet "RESULT" ):

Badge No.	Date	In	out	Break	Hours Worked
1022​	10/11/2021​	8:11​	16:32​		8​
1022​	11/11/2021​	21:05​	6:22​		8​
2560​	10/11/2021​	10:32​			missing in/out
2560​	04/11/2021​	19:08​			missing in/out
2560​	05/11/2021​	8:22​	18:53​		10​
2560​	08/11/2021​	8:24​	18:20​		10​
2560​	09/11/2021​	8:25​	20:48​		12​

shifts

06:00:00​

08:00:00​

21:00:00​

VBA that i have ( partially is working ):

Option Explicit

Sub Calculations()

Dim i, j, z As Integer
Dim lRow, lRow2 As Long
Dim sh, sh2 As Worksheet
Dim sD, eD As Date
Dim rVal As Integer

Set sh = Sheets("CLOCK")
Set sh2 = Sheets("RESULTS")

lRow = sh.Cells(Rows.Count, 1).End(xlUp).Row



For i = 2 To lRow Step 1
 j = i - 2 'not using
    If sh.Cells(i, 1) = sh.Cells(i + 1, 1) And sh.Cells(i, 2) = sh.Cells(i + 1, 2) Then
        lRow2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row
        lRow2 = lRow2 + 1
    
       
     sh2.Cells(lRow2, 1) = sh.Cells(i, 1) 'badge
     sh2.Cells(lRow2, 2) = sh.Cells(i, 2) 'date
     sh2.Cells(lRow2, 3) = sh.Cells(i, 3) 'hour
     
        
            sh2.Cells(lRow2, 4) = sh.Cells(i, 3).Offset(1, 0)
        
     
     sD = sh2.Cells(lRow2, 3)
     eD = sh2.Cells(lRow2, 4)
     rVal = DateDiff("h", sD, eD)
     
     
     sh2.Cells(lRow2, 6) = rVal
     
     Else
     
     If sh.Cells(i, 1) = sh.Cells(i + 1, 1) And sh.Cells(i, 3).Value > TimeValue("09:00:00 PM")  Then 'condition to get the 3d shift right as IN
        lRow2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row
        lRow2 = lRow2 + 1
     sh2.Cells(lRow2, 1) = sh.Cells(i, 1) 'badge
     sh2.Cells(lRow2, 2) = sh.Cells(i, 2) 'date
     sh2.Cells(lRow2, 3) = sh.Cells(i, 3) 'hour
     
        
            sh2.Cells(lRow2, 4) = sh.Cells(i, 3).Offset(1, 0)
        
     
     sD = sh2.Cells(lRow2, 3)
     eD = sh2.Cells(lRow2, 4)
     rVal = DateDiff("h", eD, sD)
     rVal = rVal - 7
     
     
     sh2.Cells(lRow2, 6) = rVal
            
      End If
      
     
     
     
     
    End If
    
    
   

Next i

End Sub

This portion of the code don`t seem to work correctly : If sh.Cells(i, 1) = sh.Cells(i + 1, 1) And sh.Cells(i, 3).Value > TimeValue("09:00:00 PM") Then

I`ve searched a lot but i don`t seem to get it right.

Please help me out.

Thanks in advance.

 

[colaps]

any hints on how to deal with the time difference part of the code are welcomed.

thank you.