Forum Discussion
Peter_2020
Helper III
2 years agoDATEDIFF with specific conditions
Hi all, I would like to ask you for help with following situation: I have the table with the list of the product numbers and for each product I have START and END date/time. And I want to calcula...
- 2 years ago
Peter_2020
I just got some time so I re wrote the formula with break time and based on minute calcualtion. please check the attached file:Production Time = VAR __SHIFTSTART = TIME( 6,0,0 ) -- Provide shift start hour VAR __SHIFTEND = TIME( 22,0,0 ) -- Provide shift end hour VAR __START = Production[Start] VAR __END = Production[End] VAR __PERIOD = FILTER( GENERATESERIES( __START, __END, 1/24/60 ) , VAR __TimeVal = MOD( [Value] , 1 ) RETURN NOT WEEKDAY( [Value] , 1) IN {1,7} && __TimeVal >= __SHIFTSTART && __TimeVal <= __SHIFTEND && ISEMPTY( FILTER( Breaks, AND( __TimeVal >= Breaks[Break Start] , __TimeVal <= Breaks[Break End] ) ) ) ) VAR __TOTALMIN = COUNTROWS( __PERIOD ) VAR __RESULT = FORMAT( INT( DIVIDE(__TOTALMIN , 60) ) , "00:" ) & FORMAT( MOD(__TOTALMIN , 60 ) , "00" ) RETURN __RESULT
Ahmedx
Super User
2 years agoshare an example with several products