# 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

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.