Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Calculating processing time in workdays + time in decimals

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...

 

2 Replies