PQ Calculate Network date/time in hours

[RattlingCarp3048]

i need to calculate the difference between 2 date/time columns with the returned value in hours rounded to one decimal place. i have tried several different codes from online, below is the most promising thus far. i opened a blank query and created a custom function using the below code. However, when i invoke the function to test i get an error and when i go back to the query with my data, create a custom column and add the function all i get are errors returned. everything is in date/time/table formats so i'm not sure where i am going wrong.

(StartDate as datetime, EndDate as datetime, BusinessHoursStart as number, BusinessHoursEnd as number, Holidays as table) =>
let
//create date list
DateList = List.Generate(() => StartDate,
each _ <= EndDate,
each _ = #duration(0,0,60,0)),
//convert date list to table
DateListTable =
Table.FromList(DateList, Splitter.SplitByNothing()),
//convert to datetime
DateTimeList =
Table.TransformColumnTypes(DateListTable, {"Column1", type datetime}),
//add weekday column
Weekdays =
Table.AddColumn(DateTimeList, "Weekday", each Date.DayOfWeek([Column1]), Int32.Type),
//exclude weekends
NoWeekends =
Table.SelectRows(Weekdays, each ([Weekday] <> 0 and [Weekday] <> 6)),
//add time column
TimeColAdded =
Table.AddColumn(NoWeekends, "Time", each DateTime.Time([Column1])),
//limited to working hours
WorkingHours =
Table.SelectRows(TimeColAdded, each [Time] > #time(BusinessHoursStart,0,0) and [Time] <= #time(BusinessHoursEnd,0,0)),
//convert date time to date
DateConversion =
Table.TransformColumns(WorkingHours, {{"Column1", DateTime.Date, type date}}),
//exclude holidays
ExclHolidays =
Table.NestedJoin(DateConversion,("Column1"), Holidays, ("Holidays"), "Holidays", JoinKind.LeftAnti),
//count rows
RowCount = Number.Round(Table.RowCount(WorkingHours)/60,1),
ReturnCount = if RowCount <0 then 0 else RowCount

in
ReturnCount

 

[AlienSx]

Hi, don't know why your code is not working. Some parts are really strange (e.g. ExclHolidays with ("Column1") syntax). Try this instead but make sure to pass optional holidays as a list of dates (not table)

(start as datetime, end as datetime, open as number, close as number, optional holidays as list) => 
let
    // work_day checks if date is business day
    work_day = (d as date) => 1 - (Number.From(Date.DayOfWeek(d, Day.Monday) > 4 or List.Contains(holidays ?? {}, d))),
    // full day hours
    full_day = close - open,
    // open and close times
    open_time = #time(open, 0, 0),
    close_time = #time(close, 0, 0),
    // start and end dates
    sd = DateTime.Date(start),
    ed = DateTime.Date(end),
    // interate over days and calculate hours
    all_dates = List.Generate(
        () => [d = sd, h = Duration.TotalHours(close_time - List.Max({open_time, DateTime.Time(start)}))],
        (x) => x[d] <= ed,
        (x) =>
            [d = Date.AddDays(x[d], 1),
            h = if d <> ed then full_day else Duration.TotalHours(List.Min({close_time, DateTime.Time(end)}) - open_time)],
        (x) => List.Max({x[h], 0}) * work_day(x[d])
    ),
    // check if agruments are wrong otherwise return calculated hours
    z = if List.AnyTrue({end <= start, close <= open}) then 0 else Number.Round(List.Sum(all_dates), 1)
in
    z

 

[AlienSx]

ouch... I forgot to consider case when start and end dates are the same so that our calculation should be based on Duration.TotalHours of the difference between end and start datetimes. I can't modify my original message so please modify "z" step as follows:

    // check if agruments are wrong or start and end dates are equal otherwise 
    // return calculated hours using iteration
    z = 
        if List.AnyTrue({end <= start, close <= open}) then 0 
        else if sd = ed then Number.Round(Duration.TotalHours(end - start), 1)
        else Number.Round(List.Sum(all_dates), 1)

 

[RattlingCarp3048]

ouch... I forgot to consider case when start and end dates are the same so that our calculation should be based on Duration.TotalHours of the difference between end and start datetimes. I can't modify my original message so please modify "z" step as follows:

    // check if agruments are wrong or start and end dates are equal otherwise
    // return calculated hours using iteration
    z =
        if List.AnyTrue({end <= start, close <= open}) then 0
        else if sd = ed then Number.Round(Duration.TotalHours(end - start), 1)
        else Number.Round(List.Sum(all_dates), 1)

its running super slow but looks to be working as expected thank you!!