Calculating Work Hour per Day

Help Studio
#1

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

image
image737×151 3.2 KB

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:

image
image1921×77 3.19 KB

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?

#2

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

#3

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.