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
Fowmy
Super User
2 years agoPeter_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
Peter_2020
Helper III
2 years agoFowmy it works perfectly. Thank you so much for your help!