Calculating Work Hour per Day

I have a list which consists of different students’ check-in check-out dates and times like below:

As you can see, check-in and out times are in same column with different rows. Also each student can check-in and out multiple times in a day.

I have to calculate work hour per day for each student and then find count of day, dates and work hour which is more than 11 hours and less than 7 hours and make a table like below:

I have an excel macro to do that which is given by my friend but macro doesn’t do proper calculation because Pre_Daily_WorkDate variable is not reseted between people so macro confuses people sometimes:

  Sub CheckinCheckOutCalculation()

ResultSheet.Range("A2", "N1000").Clear

ListSheet.Range("A1", "J5000").Sort Key1:=ListSheet.Range("D1"), Order1:=xlAscending, Header:=xlYes

i = 2
Name = ""
ResultSheet_Row = 2
Daily_WorkMinute = 0
Daily_WorkHour = 0

Dim Date_In, Date_Out, Pre_Daily_WorkDate As Date


Do While ListSheet.Cells(i, 4) <> "" And ListSheet.Cells(i, 4) <> "0"

If Name = "" Then
    
    Name = ListSheet.Cells(i, 4)
    ResultSheet.Cells(ResultSheet_Row, 1) = Name
    ResultSheet.Cells(ResultSheet_Row, 2) = 0
    ResultSheet.Cells(ResultSheet_Row, 5) = 0
    
ElseIf Name <> ListSheet.Cells(i, 4) Then
    
    Name = ListSheet.Cells(i, 4)
    ResultSheet_Row = ResultSheet_Row + 1
    ResultSheet.Cells(ResultSheet_Row, 1) = Name
    ResultSheet.Cells(ResultSheet_Row, 2) = 0
    ResultSheet.Cells(ResultSheet_Row, 5) = 0
End If


If ListSheet.Cells(i, 5) = "In" Then
    Date_In = ListSheet.Cells(i, 2)
ElseIf ListSheet.Cells(i, 5) = "Out" Then
    Date_Out = ListSheet.Cells(i, 2)
Else
    GoTo skip
End If
    
If Day(Date_Out) = Day(Date_In) And Date_Out > Date_In Then
    Daily_WorkDate = Date_Out - Date_In
    Daily_WorkMinute = Hour(Daily_WorkDate) * 60 + Minute(Daily_WorkDate) + Daily_WorkMinute
    
    If Day(Date_Out) <> Day(Pre_Daily_WorkDate) Then
        
        Daily_WorkMinute = Daily_WorkMinute - Hour(Daily_WorkDate) * 60 - Minute(Daily_WorkDate)
        Daily_WorkHour = Format(Daily_WorkMinute * 60 / 86400, "hh:mm")
        
        If Daily_WorkMinute > 705 Then
            ResultSheet.Cells(ResultSheet_Row, 2) = ResultSheet.Cells(ResultSheet_Row, 9) + 1
            ResultSheet.Cells(ResultSheet_Row, 3) = ResultSheet.Cells(ResultSheet_Row, 10) & vbCrLf & Format(Pre_Daily_WorkDate, "dd.mm.yyyy")
            ResultSheet.Cells(ResultSheet_Row, 4) = ResultSheet.Cells(ResultSheet_Row, 11) & vbCrLf & Daily_WorkHour
            
        End If
        
        If Daily_WorkMinute > 0 And Daily_WorkMinute < 420 Then
            ResultSheet.Cells(ResultSheet_Row, 5) = ResultSheet.Cells(ResultSheet_Row, 12) + 1
            ResultSheet.Cells(ResultSheet_Row, 6) = ResultSheet.Cells(ResultSheet_Row, 13) & vbCrLf & Format(Pre_Daily_WorkDate, "dd.mm.yyyy")
            ResultSheet.Cells(ResultSheet_Row, 7) = ResultSheet.Cells(ResultSheet_Row, 14) & vbCrLf & Daily_WorkHour
            
        End If
        
        Daily_WorkMinute = 0
        Daily_WorkHour = 0
        Daily_WorkMinute = Hour(Daily_WorkDate) * 60 + Minute(Daily_WorkDate)
        Pre_Daily_WorkDate = Date_Out
    End If
End If

skip:
i = i + 1
Loop

End Sub

I have no idea about vba programming. So, I couldn’t make a decision to do this calculation with Uipath or VBA.

Do you have any suggestion about this?

what do you mean by “Day Count more 11 hours” and “Dates More 11 hours” in your results table, can you give an example?

For example Liz worked 12 hours, 13.5 hours and 6 hours. Day count more than 11 hours a day will be 2, day count less than 7 hours will be 1. And dates are the dates when she worked more than 11 hours and less than 7 hours.