time difference between 2 rows

[Anonymous]

Hi

who can help me?

I have a table like this:

ID | EMPLOYEE | |IN/OUT | Date | Time
1    1002        IN       2017-01-01 08:00
2    ......
5    1002        OUT      2017-01-01 18:00
6    ......

Now i want to know the attendance time for the EMPLOYEE  keeping in mind that there a more rows for another people

It also can happen that people works in a nightshift

ID | EMPLOYEE | |IN/OUT | Date | Time
1    1002        IN       2017-01-01 22:00
2    .....
5    1002        OUT      2017-01-02 07:00
6    .....

Also then i want to know the time

Wiht kind regards,

Norbertus

[Anonymous]

Hi @Anonymous,

You can refer to below formula to achieve your requirement.

Table:

Measure:

diff = 
var current_user=LASTNONBLANK(Record[User],[User])
var current_date=MAX(Record[DateTime])
var current_state=LASTNONBLANK(Record[Actio],[Actio])
var min_date=SWITCH(current_state,"Login",current_date,"Logoff",MAXX(FILTER(ALL(Record),[User]=current_user&&[DateTime]<current_date),[DateTime]))
var max_date=SWITCH(current_state,"Login",MINX(FILTER(ALL(Record),[User]=current_user&&[DateTime]>current_date),[DateTime]),"Logoff",current_date)
return
DATEDIFF(min_date,max_date,MINUTE)

Result:

Notice: datetime are merged by date and time column, 'user' = 'employee', 'action' = 'in/out'.

Regards,

Xiaoxin Sheng

[Anonymous]

what if we have more than 1 login and log off for the same employee in the same day, so u have any solution for that

[Anonymous]

help please as for the same employee i have more than login and log off for the same day and I need to calculate networking hours ( latest exit time - earliest entry time  ) for same-day and same employee 

[TomMartens]

Hey,

wondering if its possible that there are more ins and outs for one person, or a little rephrased. Your are looking for the duration a a person has been in when leaving (out), whereas this duration is the difference between the in and its immediate following out.

Right?

[Anonymous]

hi,

There are more Employees so i don't know which row is the OUT time for one specific Employee.
I need to know the time between IN and OUT for one Employee

[MarcelBeug · Accepted solution]

In Power Query you can:

1. Combine Date and Time to DateTime.
2. Sort on employee and DateTime.
3. Add 2 indices starting with 0 and 1.
4. Merge the table with itself using the first and second index as key, so you have the previous values on the same row as the current values.
5. Add a column with the DateTime minus the previous DateTime if the employee is the same and the previous was IN and the current is OUT, else null.
6. Sort on ID to restore the original sort (not really necessary).
7. Remove columns that are no longer needed.

let
    Source = Table1,
    #"Inserted Merged Date and Time" = Table.AddColumn(Source, "DateTime", each [Date] & [Time], type datetime),
    #"Sorted Rows" = Table.Sort(#"Inserted Merged Date and Time",{{"EMPLOYEE", Order.Ascending}, {"DateTime", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index"},#"Added Index1",{"Index.1"},"Prev",JoinKind.LeftOuter),
    #"Expanded Prev" = Table.ExpandTableColumn(#"Merged Queries", "Prev", {"EMPLOYEE", "IN/OUT", "DateTime"}, {"Prev.EMPLOYEE", "Prev.IN/OUT", "Prev.DateTime"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Prev", "TimeDifference", each if [EMPLOYEE] = [Prev.EMPLOYEE] and [#"Prev.IN/OUT"] = "IN" and [#"IN/OUT"] = "OUT" then [DateTime] - [Prev.DateTime] else null, type nullable duration),
    #"Sorted Rows1" = Table.Sort(#"Added Custom",{{"ID", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows1",{"DateTime", "Index", "Index.1", "Prev.EMPLOYEE", "Prev.IN/OUT", "Prev.DateTime"})
in
    #"Removed Columns"