Calculate Hours in factory by using Excel VBA

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 stayed in the day they come to the factory.

I wrote a macro and defined each person as sicil_no since its a unique number. Since there are multiple entries and exits on the same date, I need to calculate for each date and sicil_no (person) first count number of entries and exits then subtract then add them to find total time spent in factory.

Example of my raw data.

GECIS TARIHI	SICIL NUMARASI	SOYADI	ADI	GEÇİŞ YÖNÜ
04 03 2021 07:06:25	02491	JOHN	CAN	Entry
04 03 2021 09:28:01	02312	PLAT	JULY	Entry
04 03 2021 15:50:22	02312	PLAT	JULY	Exit
04 03 2021 17:08:48	02491	JOHN	CAN	Exit
08 03 2021 07:06:45	02312	PLAT	JULY	Entry
08 03 2021 07:53:37	02260	BABER	YOKY	Entry
08 03 2021 13:05:38	02312	PLAT	JULY	Exit
08 03 2021 13:18:30	02312	PLAT	JULY	Entry
08 03 2021 17:23:01	02312	PLAT	JULY	Exit
08 03 2021 19:37:36	02260	BABER	YOKY	Exit

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

Please don't post links to files. Instead include everything that is needed to understand your question into the question itself. If you want to show something you can add screenshots. See How to Ask. — Pᴇʜ
Seems like an excel formula could do it easily. =24*(FirstOut-FirstIn+IF(SecondOut<>"",SecondOut-SecondIn,0)+IF(ThirdOut<>"",ThirdOut-ThirdIn,0)+IF(FourthOut<>"",FourthOut-FourthIn,0)) Explanation: If a person takes no breaks, the FirstOut is them leaving at the end of the day. Otherwise, its the start of their first break. The last Out is them going home, whether thats the First or FourthOut. — Toddleson
@Toddleson how should i define second out second and other variables? and should i autofill for the next 6600 row? I write your formula on cell but it gives "name" error — nesrin

Pᴇʜ Pᴇʜ · Accepted Answer · 2021-05-06T16:44:48

If your data is consistent (and you have one Entry and one Exit each day for each person) it should work with the following formula to calculate how many hours in total a person was in the factory (see column D)

=(SUMIFS(A:A,C:C,C:C,J:J,"Exit")-SUMIFS(A:A,C:C,C:C,J:J,"Entry"))*24

and with

=(SUMIFS(A:A,C:C,C:C,J:J,"Exit",A:A,">="&INT(A:A),A:A,"<"&INT(A:A)+1)-SUMIFS(A:A,C:C,C:C,J:J,"Entry",A:A,">="&INT(A:A),A:A,"<"&INT(A:A)+1))*24

you can see how many hours a person was in the factory in each day (see column E).

To show how it works with the example you gave:

Column F (total hours)
=(SUMIFS(A:A,C:C,C:C,E:E,"Exit")-SUMIFS(A:A,C:C,C:C,E:E,"Entry"))*24
Column G (daily hours)
=(SUMIFS(A:A,C:C,C:C,E:E,"Exit",A:A,">="&INT(A:A),A:A,"<"&INT(A:A)+1)-SUMIFS(A:A,C:C,C:C,E:E,"Entry",A:A,">="&INT(A:A),A:A,"<"&INT(A:A)+1))*24
Column H (corrected total hours)
=(SUMIFS(A:A,C:C,C:C,E:E,"Exit")-SUMIFS(A:A,C:C,C:C,E:E,"Entry")+IF(COUNTIFS(C:C,C:C,E:E,"Entry")>COUNTIFS(C:C,C:C,E:E,"Exit"),MAXIFS(A:A,C:C,C:C,E:E,"Entry"),0))*24

Note that if there is a Entry that has no corresponding Exit this might occur in the end. Then you need to remove that last Entry from the calculation, to calculate properly. This is what I tried with the corrected formula.

See that last Entry in red has no Exit. The formula in H will remove that Entry from calculation.

Calculate Hours in factory by using Excel VBA

1 Answers