Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

How to calculate time difference between 2 dates/times without working hours?

I want to calculate the time difference between 2 times, but only counting in working times.
 
For example Real endtime= 12-2-2022 16:00 and  Required endtime= 13-2-2022 8:20. 
Working hours are from 8:00 to 17:00, so the time difference in this case is 1:20.
It should be possible to show both positive and negative time difference.

I used this formula(where the problem is) to get the timedifference without taking working hours into account.
timedifference sec = DATEDIFF(workorder[Real_endtime],workorder[required_endtime],SECOND)
 
After that i used following formula to get the time in seconds to hh:mm:ss. This one worked well.
Timedifference hour/min/sec =
var Hour=QUOTIENT([timedifference sec],3600)
var Minute=QUOTIENT([timedifference sec]-Hour*3600,60)
var Second=bw_Cofiwerkorders[timedifference sec]-Hour*3600-Minute*60

return

CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(Hour,":"),Minute),":"),Second)


Need Help