Forum Discussion
Solved
Date / Time difference excluding weekends and factoring working hours
Hello, After seeing this following post and in order to not reopen it, I tried the function explained at the bottom but it returns sometimes suspicious values. https://community.fabric.microsof...
so complicated...
(from, to, optional st, optional et) => [s_time = st ?? #time(8, 0, 0), e_time = et ?? #time(17, 0, 0), start = List.Min({from, to}), end = List.Max({from, to}), start_date = Date.From(start), end_date = Date.From(end), gen = List.Generate( () => [c = start_date, sod = List.Max({start, start_date & s_time}), eod = List.Min({end, c & e_time})], (x) => x[c] <= end_date, (x) => [c = Date.AddDays(x[c], 1), sod = c & s_time, eod = List.Min({end, c & e_time})], (x) => if Date.DayOfWeek(x[c], Day.Monday) > 4 then null else x[eod] - x[sod] ), result = if List.NonNullCount({from, to}) < 2 then null else List.Sum(gen)][result]
so complicated...
(from, to, optional st, optional et) =>
[s_time = st ?? #time(8, 0, 0),
e_time = et ?? #time(17, 0, 0),
start = List.Min({from, to}),
end = List.Max({from, to}),
start_date = Date.From(start),
end_date = Date.From(end),
gen = List.Generate(
() => [c = start_date, sod = List.Max({start, start_date & s_time}), eod = List.Min({end, c & e_time})],
(x) => x[c] <= end_date,
(x) => [c = Date.AddDays(x[c], 1), sod = c & s_time, eod = List.Min({end, c & e_time})],
(x) => if Date.DayOfWeek(x[c], Day.Monday) > 4 then null else x[eod] - x[sod]
),
result = if List.NonNullCount({from, to}) < 2 then null else List.Sum(gen)][result]Great solution. Thanks for providing.
Hello,
I tried your solution but it seems to have problems in it :
Some results are wrong (between 3 and 17, there's not only 3 days 😞 )
that is DURATION where 1 day = 24 hours. If you want solution in (working) hours then apply Duration.TotalHours.
