Forum Discussion
Calculating a separate duration from within times
- 4 years ago
Hello - this is how you can calculate it using Power Query... I have created two parameters for the core hours start and end times. Then I calculated the greater of the join time and start time; and the earlier of the leave time and end time. If the end time is less than the start time the result is zero, otherwise the result is the difference.
SCRIPT
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY67DcAgEMV2uRqJ+0J4qyD2XyMkIkpznQvL8pxk1auyKhUShnWYPyhggXdaJVPawYFoieIKjlN5Ma3Eh9e2ssr4KwLdL+sG", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Join Date" = _t, #"Join Time" = _t, #"Leave Time" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Join Date", type date}, {"Join Time", type time}, {"Leave Time", type time}}), DurationDuringCoreHours = Table.AddColumn ( #"Changed Type", "Core Time", each let Earliest_LeaveVsEndTime = List.Min({CoreHoursEndTime, [Leave Time]}), Latest_JoinVsStartTime = List.Max({CoreHoursStartTime, [Join Time]}), CoreHoursDuration = if Earliest_LeaveVsEndTime < CoreHoursEndTime then 0 else Earliest_LeaveVsEndTime - Latest_JoinVsStartTime in CoreHoursDuration, Time.Type ) in DurationDuringCoreHours
Hello - this is how you can calculate it using Power Query... I have created two parameters for the core hours start and end times. Then I calculated the greater of the join time and start time; and the earlier of the leave time and end time. If the end time is less than the start time the result is zero, otherwise the result is the difference.
SCRIPT
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY67DcAgEMV2uRqJ+0J4qyD2XyMkIkpznQvL8pxk1auyKhUShnWYPyhggXdaJVPawYFoieIKjlN5Ma3Eh9e2ssr4KwLdL+sG", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Join Date" = _t, #"Join Time" = _t, #"Leave Time" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Join Date", type date}, {"Join Time", type time}, {"Leave Time", type time}}),
DurationDuringCoreHours = Table.AddColumn ( #"Changed Type", "Core Time", each
let
Earliest_LeaveVsEndTime = List.Min({CoreHoursEndTime, [Leave Time]}),
Latest_JoinVsStartTime = List.Max({CoreHoursStartTime, [Join Time]}),
CoreHoursDuration = if Earliest_LeaveVsEndTime < CoreHoursEndTime then 0 else Earliest_LeaveVsEndTime - Latest_JoinVsStartTime
in CoreHoursDuration, Time.Type )
in
DurationDuringCoreHours
Hi! Thank you so very much for the super quick response and solution. I really, really appreciate you sending that my way! I hope you have a wonderful day!