Calculate the time difference from Work

[Arne84]

Hi, i have some Data where the employee checks in and out. it is possible that he has a night shift. How can I calculate the time difference between come and go?

Thank you in advance.

Date	ID	Time	Status
29.06.2022	12345	22:20:19	leave
28.06.2022	12345	18:17:23	come
23.06.2022	12345	15:21:14	leave
23.06.2022	12345	07:50:03	come
22.06.2022	12345	14:12:56	leave
22.06.2022	12345	07:48:51	come
14.06.2022	12345	15:12:07	leave
14.06.2022	12345	08:45:07	come
13.06.2022	12345	15:26:06	leave
13.06.2022	12345	08:48:38	come
09.06.2022	12345	16:27:38	leave
09.06.2022	12345	08:41:33	come

[Vijay_A_Verma · Accepted solution]

Below is PQ solution. See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdExDsMgDIXhuzBHCn5gcN5VogxRla1Vt56/tJHaBGfCw6dfBuY5lBHTiAiEIQhS1nYCRKRMbbxv62sLy/CF1kMxSiVSG2/Px88l55QQSu6DDlZqZOx7cL1MAbX0PQcrs1Hl3JN8sV/rxdr1PDRm3d2xd3nfwtjv56F99kt27rn/kELUnR1zzrWaMP1fb3kD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, ID = _t, Time = _t, Status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"ID", Int64.Type}, {"Time", type time}, {"Status", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Status", Order.Ascending},{"Date", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
    #"Calculated Modulo" = Table.TransformColumns(#"Added Index", {{"Index", each Number.Mod(_, Table.RowCount(#"Added Index")/2), type number}}),
    Leave = Table.SelectRows(#"Calculated Modulo", each ([Status] = "leave")),
    Come = Table.SelectRows(#"Calculated Modulo", each ([Status] = "come")),
    #"Renamed Columns" = Table.RenameColumns(Come,{{"Time", "Incoming Time"}, {"Date", "Incoming Date"}}),
    #"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"Index"}, Leave, {"Index"}, "Leave", JoinKind.LeftOuter),
    #"Expanded Leave" = Table.ExpandTableColumn(#"Merged Queries", "Leave", {"Date", "Time"}, {"Outgoing Date", "Outgoing Time"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Leave",{"Index", "Status"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"ID", "Incoming Date", "Incoming Time", "Outgoing Date", "Outgoing Time"}),
    #"Added Custom" = Table.AddColumn(#"Reordered Columns", "Time Diff in Hours", each Duration.TotalHours([Outgoing Date]&[Outgoing Time]-([Incoming Date]&[Incoming Time])))
in
    #"Added Custom"

[PurpleGate]

And if you only want to see how long they have been at the office

Time at office = IF (
        'Table'[status] = "leave",
        VAR leave =
            CALCULATE (
                MAX ( 'Table'[DAteTime] ),
                ALLEXCEPT ( 'Table', 'Table'[ID] ),
                'Table'[DAteTime] < EARLIER ( 'Table'[DAteTime] )
            )
        RETURN
            IF ( NOT ISBLANK ( leave ), FORMAT ( 'Table'[DAteTime] - leave, "HH:MM" ) )
    )

[PurpleGate]

Hi,

1. In Power Query I made a DateTimestamp

Custom Column > Date and Time (note that I had them both as text fields when I joined them. Afterwards I changed it to DateTimeStamp)

=[Date]&" "&[Time]

2. I added a calculated column

Time Between =
IF (
    'Table'[status] = "come",
    VAR come =
        CALCULATE (
            MAX ( 'Table'[DAteTime] ),
            ALLEXCEPT ( 'Table', 'Table'[ID] ),
            'Table'[DAteTime] < EARLIER ( 'Table'[DAteTime] )
        )
    RETURN
        IF ( NOT ISBLANK ( come ), FORMAT ( 'Table'[DAteTime] - come, "HH:MM" ) ),
    IF (
        'Table'[status] = "leave",
        VAR leave =
            CALCULATE (
                MAX ( 'Table'[DAteTime] ),
                ALLEXCEPT ( 'Table', 'Table'[ID] ),
                'Table'[DAteTime] < EARLIER ( 'Table'[DAteTime] )
            )
        RETURN
            IF ( NOT ISBLANK ( leave ), FORMAT ( 'Table'[DAteTime] - leave, "HH:MM" ) )
    )
)