Forum Discussion
Date / Time difference excluding weekends and factoring working hours
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]
Hi, I'm not sure if I understand your question. Could you be more specific please? Add some screenshots with detailed explenation please.
I declare this function and tried to use it as following :
#"Step5" = Table.AddColumn(#"Step4", "delay", each fn_WorkingDuration(#"Step4",[début],[dateEC], "dateEC duration"))
but I got this issue :
Expression.Error : Sorry... We could not convert the value #datetime(2022, 9, 19, 8, 0, 0) in Text type
Détails :
Value=19/09/2022 08:00:00
Type=[Type]
If you want to use it as a separate function check queries in attached file.
I removed ChangedType because in function, it allows only 3 or 4 parameter. I let Helper but I got this now :
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 nameI refreshed the post, but in your function, I still see only 4 parameters :
Have you downloaded attached file as I mentioned?
Yes,
opening it with excel, file is empty
opening it with Pbi, the source has only two steps one empty sheet and no function found ....
I wonder if I open it well...
Trus me, the file is not empty 😉