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]
I've edited my Function - there are different parameters. Check my previous post again, download attached .xlsx file and use that function.
In that function you have to declare parameters:
1.) tbl (usualy previous step - or step before Helper step) this must be a table
2.) h (Helper step) - this must be a record
3.) start dateTime (column name with start dateTime) as text
4.) end dateTime (column name with start dateTime) as text
5.) optional - new column name
I refreshed the post, but in your function, I still see only 4 parameters :