Forum Discussion
Solved
Calculate the time difference from Work
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 |
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"
3 Replies
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" ) ) ) )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" ) ) )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"
