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]
Difference in time. After I can convert in days if I have to.
Examples :
18/09/2024 08:00:00 -> 18/09/2024 10:00:00 = 02:00:00
18/09/2024 08:00:00 -> 19/09/2024 10:00:00 = 11:00:00 according working between 08:00:00 and 17:00:00 each day (so 08:00:00 -> 17:00:00 + 08:00:00 -> 10:00:00)
Check this. Ignore first 4 rows (I've added them just for test purpose).
You can edit shiftStart and shiftEnd in Helper step.
EDIT: I've added option where you can decide whether you want to include or exclude weekends:
Output
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nVZRbuwwCLxKtd+rLGA7iX2Vam9Q9e/d/zng2NQmaVJppRUTDMMATj4/H7i+IL4IiD5gTgESwOP5IKgorskXtPzez3wsmsfcjWPxT8cQkisOGqWKHmRb89PxmE+ZBlJGYcp17MZWPm4u7gOXBJjCYh3M6JYGdvr+R5rlBxqT22K4CZvx/e/ry/KEKPTJjNtQYeGCos/GHld58kMIwne24ioUkatzEtc1Y+5S+2ChFM/Udy+E2sr8y8U+H/NEzdj0XIv6IeWz0iADvVYNNKoTUDUQuoIdWmg4q6YoXf7U1nAXE64H6NWIe2DXhyiTF83a4zhPXDs0o7lsYpZ5HFCcL+6ReJc0SzVGF7RQwIvVxEZ1Ajzh7S00yPibCkPbFWKZgj9Ab1ws3qvNZAPzWFIb4jyMvrttpUdwRxBPShAx+tK9KYi7pTsElUaMQeHZTC6LqvegpdG1CylQU+R6F0F70RgNJQ1eWNT4Qqz7tO25s9AyMHZEWjriPDE4YawGrX1fcefAU4SW7gqVW9uvIshaDe3CpYOJ5jv7+PVkb4Hrek50h2lAxZSNgZOLJopnQmMnNOkJYYOodi4zkc5xP9VnhVqrPwkiHbwsCJPz5RUazbc31O+NAQ3zrTTLr2n4nhnTXNX9oJrBha/jEYU71RCeVYP1WhjQe72h+GsaXtcBLWt16Z7BbgL5M2pAs7Dv938=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [début = _t, dateEC = _t, DiffEC = _t, dateT = _t, dateF = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"dateF", type datetime}, {"dateT", type datetime}, {"DiffEC", type duration}, {"dateEC", type datetime}, {"début", type datetime}}),
Helper = [ shiftStart = #time(8,0,0),
shiftEnd = #time(17,0,0),
includeWeekends = "no", //type "yes" if you want to include weekends. Type "no" if you want to exclude weekends
workingHours = shiftEnd - shiftStart ],
fn_WorkingDuration =
(previousStep as table, start as text, end as text, optional colName as text)=>
let
// Ad_StartColumn = Table.DuplicateColumn(ChangedType, "début", "S"),
// Ad_EndColumn = Table.DuplicateColumn(Ad_StartColumn, "dateEC", "E"),
Ad_StartColumn = Table.DuplicateColumn(previousStep, start, "S"),
Ad_EndColumn = Table.DuplicateColumn(Ad_StartColumn, end, "E"),
Ad_SDate = Table.AddColumn(Ad_EndColumn, "S Date", each DateTime.Date([S]), type date),
Ad_STime = Table.AddColumn(Ad_SDate, "S Time", each DateTime.Time([S]), type time),
Ad_EDate = Table.AddColumn(Ad_STime, "E Date", each DateTime.Date([E]), type date),
Ad_ETime = Table.AddColumn(Ad_EDate, "E Time", each DateTime.Time([E]), type time),
// Weekends excluded
Ad_WorkingDays = Table.AddColumn(Ad_ETime, "Working Days", each try
[ a = List.Dates([S Date], Duration.TotalDays([E Date] - [S Date]) +1, #duration(1,0,0,0)), //weekends included
b = List.Select(a, (x)=> Date.DayOfWeek(x, Day.Monday) < 5), //weekends excluded
c = if Text.Trim(Text.Lower(Helper[includeWeekends])) = "yes" then a else b
][c] otherwise null, type list),
// [S Date] and [E Date] excluded
Ad_FullDaysHours = Table.AddColumn(Ad_WorkingDays, "Full Days Hours", each try
[ a = List.RemoveItems([Working Days], { [S Date], [E Date]}),
b = Helper[workingHours] * List.Count(a)
][b] otherwise null, type duration),
Ad_FirstDayHours = Table.AddColumn(Ad_FullDaysHours, "Fist Day Hours", each try if [S Date] = List.First([Working Days])
then (if [S Date] = [E Date] then List.Min({ [E Time], Helper[shiftEnd] }) else Helper[shiftEnd]) - List.Max({ [S Time], Helper[shiftStart] })
else #duration(0,0,0,0) otherwise null, type duration),
Ad_LastDayHours = Table.AddColumn(Ad_FirstDayHours, "Last Day Hours", each try if [S Date] = [E Date] then #duration(0,0,0,0) else if [E Date] = List.Last([Working Days])
then Helper[workingHours]
else #duration(0,0,0,0) otherwise null, type duration),
// Ad_TotalWorkingDuration = Table.AddColumn(Ad_LastDayHours, "Total Working Duration", each [Full Days Hours] + [Fist Day Hours] + [Last Day Hours], type duration),
Ad_TotalWorkingDuration = Table.AddColumn(Ad_LastDayHours, if colName <> null then colName else "Total Working Duration" , each [Full Days Hours] + [Fist Day Hours] + [Last Day Hours], type duration),
_RemovedColumns = Table.RemoveColumns(Ad_TotalWorkingDuration,{"S", "E", "S Date", "S Time", "E Date", "E Time", "Working Days", "Full Days Hours", "Fist Day Hours", "Last Day Hours"})
in
_RemovedColumns,
StepBack = ChangedType,
Ad_dateECDuration = fn_WorkingDuration(StepBack, "début", "dateEC", "dateEC duration"),
Ad_dateTDuration = fn_WorkingDuration(Ad_dateECDuration, "début", "dateT", "dateT duration"),
Ad_dateFDuration = fn_WorkingDuration(Ad_dateTDuration, "début", "dateF", "dateF duration")
in
Ad_dateFDuration
Hello, Thanks for your help and time.
If I want to make this diff between two of my datetime field, where do I have to put them because I see start and end but they're text field, not datetime field.
Thanks
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 😉