Power Query Time difference between two dates/times columns excluding holidays

Hello

I've now adapted the function. has a 3rd parameter that requires a list of days that are representing the holidays

(StartTime as datetime, EndTime as datetime, ListHoliday as nullable list) as number =>
let
    //StartTime = #datetime(2019,12,3,8,00,00),
    //EndTime = #datetime(2019, 12, 8, 8, 0, 0),
    //ListHoliday = {"03.12.19", "04.12.19"},
    ListHolidayCheck = if ListHoliday = null then {} else ListHoliday,
    ListHolidayInternal = List.Transform(ListHolidayCheck, each Date.From(_)),
    DurationInHours = (Number.From(EndTime)-Number.From(StartTime))*24,
    DateTimesFromStartEnd = List.DateTimes(StartTime, DurationInHours, #duration(0,1,0,0)),
    FilterHolidays = List.Transform(DateTimesFromStartEnd, each if List.Contains(ListHolidayInternal, Date.From(_))= true then null else _),
    TableWithDateTime = #table({"DateTime"}, List.Zip({FilterHolidays})),
    #"Added Custom" = Table.AddColumn(TableWithDateTime, "Hours", each 1),
    #"Inserted Day of Week" = Table.AddColumn(#"Added Custom", "Day of Week", each Date.DayOfWeek([DateTime], Day.Monday), Int64.Type),
    #"Filtered Rows" = Table.SelectRows(#"Inserted Day of Week", each ([Day of Week] < 5)),
    HoursSum = List.Sum(#"Filtered Rows"[Hours])
in
    HoursSum

have fun

Jimmy

Jimmy801 thank you for this, much appreciated. Will have a go at creating this into my query model and let you know if I have any issues. If no issues, I will mark this post as 'Accepted Solution'

Jimmy801 yes that worked with the following edit

=try fxHoursBetweenDates([startdatetim], [endtdatetime], null) otherwise -99

Thank you for your help and patience, much appreciated:)

@Jimmy801 yes that worked with the following edit

=try fxHoursBetweenDates([startdatetim], [endtdatetime], null) otherwise -99

Thank you for your help and patience, much appreciated:)