Date / Time difference excluding weekends and factoring working ours

Hi Gplange,

this time I've tested more cases and I hope it works now.

let
    // calculates a duration between two timestamps in working hours
    fnDurationWorkingHours = (StartDateTime as datetime, EndDateTime as datetime) as duration =>
        let
            // start and end date
            StartDate = Date.From(StartDateTime),
            EndDate = Date.From(EndDateTime),

            // start and end time
            StartTime = Time.From(StartDateTime),
            EndTime = Time.From(EndDateTime),

            // end of the shift on the first day and start of the shift on the last day
            ShiftEndOnStartDate = #time(17, 0, 0),
            ShiftStartOnEndDate = #time(8, 0, 0),

            // working days are from Mon=0 to Fri=4
            fnIsWorkingDay = (dt as date) as logical => Date.DayOfWeek(dt, Day.Monday) < 5,

            // generate whole days between StartDateTime and EndDateTime
            DaysBetween = List.Generate(
                () => Date.AddDays(StartDate, 1),
                each _ < EndDate,
                each Date.AddDays(_, 1)
            ),

            // select only working days
            WorkingDaysBetween = List.Select(DaysBetween, fnIsWorkingDay),

            // get minimum of two times
            fnMin = (first as time, second as time) as time =>
                if first > second then second else first,

            // get maximum of two times
            fnMax = (first as time, second as time) as time =>
                if first > second then first else second,

            // duration on the first day
            StartDateWorkingHours = 
                if not fnIsWorkingDay(StartDate) then
                    #duration(0, 0, 0, 0)
                else if ShiftEndOnStartDate < StartTime then
                    #duration(0, 0, 0, 0)
                else
                    ShiftEndOnStartDate - fnMax(StartTime, ShiftStartOnEndDate),

            // duration on the days between
            WorkingHoursBetween = #duration(0, List.Count(WorkingDaysBetween) * 9, 0, 0),

            // duration on the last day
            EndDateWorkingHours = 
                if not fnIsWorkingDay(EndDate) then
                    #duration(0, 0, 0, 0)
                else if EndTime < ShiftStartOnEndDate then
                    #duration(0, 0, 0, 0)
                else
                    fnMin(EndTime, ShiftEndOnStartDate) - ShiftStartOnEndDate,

            // sum it up
            Result = 
                if StartDateTime > EndDateTime then 
                    #duration(0, 0, 0, 0)
                else if StartDate = EndDate and not fnIsWorkingDay(StartDate) then
                    #duration(0, 0, 0, 0)
                else if StartDate = EndDate then
                    fnMin(EndTime, ShiftEndOnStartDate) - fnMax(StartTime, ShiftStartOnEndDate)
                else 
                    StartDateWorkingHours +
                    WorkingHoursBetween +
                    EndDateWorkingHours
        in
            Result,

    // test data
    StartDateTime = #datetime(2019, 5, 17, 18, 46, 0),
    EndDateTime = #datetime(2019, 5, 18, 18, 39, 0)
in
    fnDurationWorkingHours(StartDateTime, EndDateTime)