Time format 37:30:55 greater than 24 hours

Power Query
11

.
Okay so Bill Szysz created a Power Query M function that does just that and here it is:

(WHStart, WHEnd, Start, End, ListOfHolidays) =>

let

    DStart             = Number.From(DateTime.Date(Start)), // start day
    TStart             = Number.From(DateTime.Time(Start)), // start time
    DEnd               = Number.From(DateTime.Date(End)),  // end day
    TEnd               = Number.From(DateTime.Time(End)),  // end time
    // List of days without saturdays and sundays
    ListOfNumbers      = List.Select({DStart..DEnd}, each Number.Mod(_,7)>1),
    // List of dates without holidays, saturdays and sundays 
    ListOfWorkingDays  = List.Difference(ListOfNumbers,ListOfHolidays),
    SumOfWorkingHours  = if DStart = DEnd then //checking if the start day is the same as the final day
                            if DStart = List.First(ListOfWorkingDays) then // checking if the start day is not a holiday (start day = end day)
                               List.Median({WHStart, WHEnd, TEnd}) - List.Median({WHStart, WHEnd, TStart}) // working hours if the start day = end day
                            else
                               0
                         else
                            (
                             if DStart = List.First(ListOfWorkingDays) then // checking if start day is working day (start day <> end day)
                                WHEnd - List.Median({WHStart,WHEnd,TStart}) // working hours (for start day)
                             else
                                0
                            )
                            +
                            (
                             if DEnd = List.Last(ListOfWorkingDays) then // checking if end day is working day (start day <> end day)
                                List.Median({WHStart,WHEnd,TEnd})- WHStart // working hours (for end day)
                             else
                                0
                            )
                            +
                      // sum of full-time working hours (start day, end day, holidays, saturdays and sundays excluded)
                            List.Count(List.Difference(ListOfWorkingDays,{DStart,DEnd}))*(WHEnd - WHStart)
in
    SumOfWorkingHours

.
Lets look at an overview of the Staging Queries in the PBIX first:

  1. FileLocation; change this into the location where you’ve saved the excel file and all queries in the PBIX file will be restored.
  2. ListOfHolidays; is used by the fxSum of Working Hours and all Dates need to be Int64.Type
  3. tHours; lists your workingday start- and endtime
  4. WDstart; parameter is used by the fxSum of Working Hours (selected from the tHours)
  5. WDend; parameter is used by the fxSum of Working Hours (selected from the tHours)
  6. fxSum of Working Hours; as shown above
    Duration%20Queries
    .
    And this is the result when applied to your data. Indicated by orange the nett result in duration (3), hours (4) and minutes (5) for the given start- (1) and end (2) date and time.
    Below that indicated by red you’ll see an example that only time between the WDstart & WDend parameters are contributing to the result in [Sum of working hours] (3)
    Nett%20Duration
    Nett%20Duration1209×415 48.8 KB

.
Here are your files. I hope this was helpful.
Sample-data (1).xlsx (73.3 KB)
eDNA Forum - Duration.pbix (59.7 KB)

2 Likes