Calculate time for each person to stays in the factory

Question

There are dates in the cell and times of entering and leaving the factory. I want to calculate how many hours each person has stay in the day they come to the factory. For this, I wrote a macro like this and I defined each person as sicil_no , but since there are multiple entries and exits at different times on the same date, I need to determine the last and first exit times for each day and subtract them. I didnt figure out how to do the last part

Sub macro()

Dim sicil_no As String Dim i As Integer Dim end_row As Long Dim dates As Range Dim gecis_yonu As String Dim entry As String Dim Exits As String

end_row = Cells(Rows.Count, 3).End(xlUp).Row


For i = 3 To end_row
sicil_no = Cells(i, 3).Value
dates = Cells(i, 1).Value

    If Range("J", i).Value = "Exit" Then
        Range("J", i).Value = exist
    End If
    
    If Range("J", i).Value = "Entry" Then
        Range("J", i).Value = entry
    End If
    


    Next

        For Each dates In Range("A", end_row)
        
        
        Range("M", i).Value = exist - entry
        
        
        
        Next

End Sub

If the in/out of workers in the factory is in the same day (before 0:00:00) you could use MIN and MAX to retrieve the lowest time value and the highest. — Damian
How should i do it? I need to do for each person on the list and for each date — nesrin
Well, a screenshoot with an example would help... We are blind here... — Damian
I am new so i dont know how to post screenshot here but smt like this, I have 10 person on my list. I have record for every one of them to come to factory in different days. every day they enter 1 time and exit 1 time some days they enter and exit more than 2 times so there might records more than 2 for some days. I want to calculate for each person's spent time in factory at that day — nesrin
You can solve this issue with pivot tables. Probably no need for code. — freeflow

Andreas Andreas · Accepted Answer · 2021-04-26T08:18:31

One possible way is to use MAXIFS and MINIFS formula to get this result:

It can probably be done better, but if you select A:H and remove duplicates and uncheck column A then you get the result you are looking for I believe.

This assumes the date in column A is a true date and not just a text. If it's not a date then you will need to make it a date. This can be done using DATEVALUE and RIGHT, LEFT, MID to make the string an accepted date format.

Then in E column you add this formula

=TEXT(A2,"YYYY-MM-DD")

In F:

=MAXIFS(A:A,E:E,TEXT(A2,"YYYY-MM-DD"),B:B,B2)

In G:

=MINIFS(A:A,E:E,TEXT(A2,"YYYY-MM-DD"),B:B,B2)

And lastly in H:

=F2-G2

When all formulas are on the sheet, select everything and copy, paste as values, then use remove duplicates like this:

and the result is this:

EDIT:
For completeness, this is how you convert your date to an accepted date format.

In M2 (example):

=MID(A2,7,4)&"-"&MID(A2,4,2)&"-"&LEFT(A2,2)&" "&RIGHT(A2,8)

then we need to use DATEVALUE and TIMEVALUE on this cell N2:

=DATEVALUE(M2)+TIMEVALUE(M2)

Calculate time for each person to stays in the factory

2 Answers