I've been struggling with calculating processing time in workdays and processing time in workhours.
i've used the following function to calculate the DateDiff in workdays between 2 dates:
= (InitialDate as date, FinalDate as date ) as number =>
let
DaysBetweenDates = Duration.Days(FinalDate-InitialDate),
DaysList = List.Dates(List.Min({InitialDate,FinalDate}),Number.Abs(DaysBetweenDates)+1, Duration.From(1)),
WeekDaysList = List.Select(DaysList, each (Date.DayOfWeek(_, Day.Monday) < 5) ),
WorkingDays = (if DaysBetweenDates < 0 then -1 else 1) * List.Count(WeekDaysList)
in
WorkingDays
now this works fine, but it'll give a processing time of 1 day, for a ticket that has been created AND closed on the same day. In those cases it's simple; closureTime - creationTime. for tickets that span multiple days this doens't work tho. Since our company's opening times are from 08:00:00 to 17:00:00, i tried to calculate the difference between the creationTime and 17:00:00 (on the creationday), and add that to the difference of 08:00:00 and the closureTime (on the closingDay) and add that SUM to the whole workdays that it had already calculated. This works fine, but i noticed some tickets were being created/closed outside of workinghours, so subtracting them gives negative values. How do i remedy this? This is what i have so far regarding the timediff:
TestDate = VAR datedifferenceone = DATEDIFF(Changes[creationDate2], Changes[closureDate2],DAY)
return
IF(Changes[creationDate2] = [closureDate2],
(Changes[weekDayDiff] -1) + (TIMEVALUE(Changes[closureTime]-Changes[creationTime])),
((Changes[weekDayDiff] -2) + (TIME(17,00,00) - Time(HOUR(Changes[creationDate2]),MINUTE(Changes[creationDate2]), SECOND(Changes[creationDate2])) + (Time(HOUR(Changes[closureDate2]), MINUTE(Changes[closureDate2]), SECOND(Changes[closureDate2]))-TIME(08,00,00))*2.667)
)
)
values:
creationDate2: 13-4-2022 21:01:25
closureDate2: 14-4-2022 08:52:00
weekDayDiff function gives: 2 days
TestDate (the code above): -0,07 workingdays (which is not correct)
I've started the following code, but i'm clueless on how to solve it;
TestToime =
var CreationCheck = IF(Changes[creationDate2] > TIME(17,00,00) || Changes[creationDate2] < TIME(08,00,00),1 , 0)
var ClosureCheck = IF(Changes[closureDate2] > TIME(17,00,00) || Changes[closureDate2] < TIME(08,00,00),1 , 0)
Return
IF(Changes[creationDate2] = [closureDate2],
(Changes[weekDayDiff] -1) + (TIMEVALUE(Changes[closureTime]-Changes[creationTime])),
((Changes[weekDayDiff] -2) + (if( CreationCheck = 1 && ClosureCheck = 1,
((TIME(17,00,00) - TIMEVALUE(Changes[creationDate2])) + (TIMEVALUE(Changes[closureDate2])-TIME(08,00,00))) *2.667 //converting decimal 24 hours to decimal 9 hour workhours, "havent filled 3e statement with something usefull yet"
)
)
)
)
Sorry for the long question...
