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?