Turn Around Time - remove afterhours

[jcastr02]

I have two columns - Created Date and Resolved Date.  I'm trying to do a turn around (difference Resolved-Created) but I'd like to exclude any hours after 4:30PM M-F; and all hours of Saturday to Sunday - to get a true turn around time.  

= Table.AddColumn(#"Added Custom", "Turn Around Time", each [ResolvedDate]-[Created])

[AlienSx · Accepted solution]

let
    // this is your table. Replace #table with a ref to your query name 
    tbl = #table(
        type table [created = datetime, resolved = datetime], 
        {{#datetime(2024, 5, 7, 8, 0, 0), #datetime(2024, 5, 14, 16, 50, 0)},
        {#datetime(2024, 5, 8, 7, 0, 0), #datetime(2024, 5, 12, 7, 30, 0)},
        {#datetime(2024, 5, 4, 10, 0, 0), #datetime(2024, 5, 6, 16, 50, 0)}}
    ),
    // this function calculates duration on-hours
    // created = Created Date as datetime
    // resolved = Resolved Date as datetime
    // open = opening time as time (say, 8:00)
    // close = closing time as time (say 16:30)
    on_time = (created as datetime, resolved as datetime, open as time, close as time) => 
        [
        // days of week correction to get next working day
        corr_open = {1, 1, 1, 1, 3, 2, 1},           
        gen = List.Generate(
            () => 
                [start = 
                    if Time.From(created) >= close 
                        or Date.DayOfWeek(created, Day.Monday) > 4
                    then Date.From(Date.AddDays(created, corr_open{Date.DayOfWeek(created, Day.Monday)})) & open
                    else if Time.From(created) < open 
                            then Date.From(created) & open 
                            else created,
                end = List.Min({resolved, Date.From(start) & close})],
            (x) => x[start] <= resolved,
            (x) => 
                [start = Date.From(Date.AddDays(x[end], corr_open{Date.DayOfWeek(x[end], Day.Monday)})) & open,
                end = List.Min({resolved, Date.From(start) & close})],
            (x) => x[end] - x[start]
        ),
        out = Duration.TotalHours(List.Sum(gen))][out],
    // here we add column with total hours
    total_hours = Table.AddColumn(
        tbl, 
        "Turn Around Time", 
        (x) => on_time(x[created], x[resolved], #time(8, 0, 0), #time(16, 30, 0)))
in
    total_hours

[Caelan]

I don't think there's a simple way to do this.

I think you'll be best splitting each date time column into two: date and time. Build a networkdays column to count the number of full work days between the two dates, (again there's no ready build function for this as far as I know, but if you search 'networkdays in power query' you will get instructions.) then another column to take the start time away from a full day - so say you work 9-5, and start time is 4, that'd be 17:00-16:00, giving 1 hour, then another column taking the start of the workday away from the finish time - so finishing at 4 on a 9-5 day would be 16:00-09:00, giving 7 hours, then add all 3 columns together.