Forum Discussion

Peter_2020's avatar
Peter_2020
Icon for Helper III rankHelper III
2 years ago
Solved

DATEDIFF 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...
  • Fowmy's avatar
    Fowmy
    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