Calculating a separate duration from within times

[tarrington]

Hi there! I'm trying to calulcate a minimum duration of time spent active within the core hours of daily meetings. As you can see below, I have Join Time, Leave Time, and Total Duration. The core hours of meetings that I'd like to account for are 10:45:00 to 11:00:00, so I'd like to confirm at least 4 minutes of activity between these times... and that's step 2. First, I need to calculate the duration from within the core hours.

If you look at row 3, this person has the full duration of 15 minutes, as they joined and left before and after the core hours.

If you look at row 5, this person joined at 10:49:05 and left at 11:01:27. Thus, their time in the core hours would be 0.00.10:55, between 10:49:05 and 11:00:00.

I'd like a new column to calculate for this duration within core hours. So row 3's result would be 0.00.15.00 and row 5's, 0.00.10:55

Could anyone help with creating this column? Thank you so very much! 

[jennratten · Accepted solution]

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

[tarrington]

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!

[ronrsnfld]

Add a column with this formula:

= List.Max(
  {#duration(0,0,0,0),
  List.Min({coreEnd,[Leave Time]}) -  List.Max({coreStart,[Join Time]})
  }
        )

Entire code to reproduce:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY67DcAgEMV2uRqJ+0J4qyD2XyMkAiUFnQvLcu9k2bOyKiUShlWYPyhggVca6aSUhQ1RDoorOFblxWMlNl7T+im8K+2rCHS+jBs=", 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}}),

        coreStart = #time(10,45,0),
        coreEnd = #time(11,0,0),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Total Duration", each Duration.From([Leave Time]-[Join Time]), type duration),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Core Time", each List.Max(
            {#duration(0,0,0,0),
                List.Min({coreEnd,[Leave Time]}) -  List.Max({coreStart,[Join Time]})}
        ))
in
    #"Added Custom1"

[tarrington]

Hi! Thank you so very much for the super quick response and answer. I really, really appreciate you sending that my way! I hope you have a wonderful day! Keep up the great work!

[Vijay_A_Verma]

Use below formula for core hours duration

= List.Max({#duration(0,0,0,0),List.Min({#time(11,0,0),[Leave Time]})-List.Max({#time(10,45,0),[Join Time]})})

This would give 15 mins for row 1, 0 for row 2nd and 4th as they didn't spend any time during core hours. 

[tarrington]

Hi there! Thank you so very much for the super quick response and answer. I really, really appreciate you sending that my way! I hope you have a wonderful day! Thanks for all the great work!