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

Answer 1

You can obtain your desired output using Power Query, available in Windows Excel 2010+ and Office 365 Excel

You did not show what you want for output, but you can add to what I have shown which is the bare minimum Sicil, Date and Time between earliest and latest times. (Assuming each pair of times is entry/exit, you could also sum the differences between each pair of times per day)

In the Query, you can sort the results depending on whether you want to show by date or by employee.

• Select some cell in your original table
• Data => Get&Transform => From Table/Range
• When the PQ UI opens, navigate to Home => Advanced Editor
• Make note of the Table Name in Line 2 of the code.
• Replace the existing code with the M-Code below
• Change the table name in line 2 of the pasted code to your "real" table name
• Examine any comments, and also the Applied Steps window, to better understand the algorithm and steps

M Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

//Add custom column with just the Date part for grouping
    #"Added Custom" = Table.AddColumn(Source, "Date", each Date.From([Dates])),

//Group by Sicil No and Date
//Then extract the time in Factory as the last time less the first time
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Sicil No", "Date"}, {
        {"Hrs in Factory", each List.Max([Dates]) - List.Min([Dates]), type duration}   
        }),
    #"Changed Type" = Table.TransformColumnTypes(#"Grouped Rows",{{"Date", type date}})
in
    #"Changed Type"

Edit

If you want to add up the actual time in the factory per day, taking into account the entry/exit times:

• Assuming times are entered as pairs, where the first time is entry and the second is exit
• Merely subtract one from the other to get each duration
• The group as above and add the total durations per Sicil and Date

M Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

//Add custom column with just the Date part for grouping
    #"Added Custom" = Table.AddColumn(Source, "Date", each Date.From([Dates])),

//Add Index column to access previous row
    #"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 0, 1, Int64.Type),

//if the Index number is an Odd number,
//   then subtract the previous row from the current row to get the Duration
    #"Added Custom1" = Table.AddColumn(#"Added Index", "Duration", each 
        if Number.Mod([Index],2)=0
            then null 
            else [Dates]- Table.Column(#"Added Index","Dates"){[Index]-1}),

//Group by Sicil and Date
//  SUM the durations
    #"Grouped Rows" = Table.Group(#"Added Custom1", {"Sicil No", "Date"}, {
        {"Time in Factory", each List.Sum([Duration]), type nullable duration}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Grouped Rows",{{"Sicil No", Int64.Type}, {"Date", type date}})
in
    #"Changed Type"

further modification to account for "real" list not being sorted as needed, and also data errors with mismatch of entry/exits
Also different routine to refer to previous row for speed improvements

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

//Change type especially datetime to Turkish culture (since I am in US)
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"GECIS TARIHI", type datetime}, {"KART NUMARASI", type any}, {"SICIL NUMARASI", Int64.Type}, {"SOYADI", type text}, 
        {"ADI", type text}, {"FİRMASI", type text}, {"GEÇİÇİ TAŞERON", type any}, {"BÖLÜM KODU", type any}, 
        {"TERMINAL", type any}, {"GEÇİŞ YÖNÜ", type text}, {"GEÇİŞ DURUMU", type any}, {"ZONE", type any}}, "tr-TR"),

//Remove columns that will not appear in final report
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"KART NUMARASI", "SOYADI", "ADI", "FİRMASI", "GEÇİÇİ TAŞERON", 
            "BÖLÜM KODU", "TERMINAL", "GEÇİŞ DURUMU", "ZONE"}),

//Sort for proper processing
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"SICIL NUMARASI", Order.Ascending}, {"GECIS TARIHI", Order.Ascending}}),

//add shifted columns to reference previous rows for entry/exit and time
//much faster than using the Index column method
    ShiftedList = {null} &  List.RemoveLastN(Table.Column(#"Sorted Rows", "GEÇİŞ YÖNÜ"),1),
    Custom1 = Table.ToColumns(#"Sorted Rows") & {ShiftedList},
    Custom2 = Table.FromColumns(Custom1, Table.ColumnNames(#"Sorted Rows") & {"GEÇİŞ YÖNÜ" & " Prev Row"}),

    ShiftedList1 = {null} &  List.RemoveLastN(Table.Column(Custom2, "GECIS TARIHI"),1),
    Custom3 = Table.ToColumns(Custom2) & {ShiftedList1},
    Custom4 = Table.FromColumns(Custom3, Table.ColumnNames(Custom2) & {"GECIS TARIHI" & " Prev Row"}),

//Calculate duration on the appropriate rows
    #"Added Custom" = Table.AddColumn(Custom4, "Time in Factory", each 
        if [GEÇİŞ YÖNÜ] = "Exit" and [GEÇİŞ YÖNÜ Prev Row] = "Entry"
            then [GECIS TARIHI] - [GECIS TARIHI Prev Row]
            else null),

//Filter out the unneeded rows
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Time in Factory] <> null)),

//Remove the offset columns
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"GEÇİŞ YÖNÜ Prev Row", "GECIS TARIHI Prev Row"}),

//add Date column for grouping
    #"Added Custom1" = Table.AddColumn(#"Removed Columns1", "Date", each DateTime.Date([GECIS TARIHI]),Date.Type),

//Group by Date and Sicil and SUM the Time in Factdory
    #"Grouped Rows" = Table.Group(#"Added Custom1", {"SICIL NUMARASI", "Date"}, {
        {"Time in Factory", each List.Sum([Time in Factory]), type duration}
        })
in
  #"Grouped Rows"

Answer 2

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)