DATEDIFF with specific conditions

[Peter_2020]

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 calculate duration between Start and End date/time but without weekends and I am working only on 2 shifts - it means from 6:00 - 14:00 first shift and 14:00 - 22:00 second shift. So time period 22:00 - 6:00 must be out of the calculation.

Here is example:

62 hours is calculated using formula with DATEDIFF, but it calculate time including weekend and 24 hours per day. By using proper formula, expected result is - 6 hours.

10.11.2023 - 18:00 - 22:00 - 4hours

11.11.2023 - weekend - 0 hours

12.11.2023 - weekend - 0 hours

13.11.2023 - 6:00 - 8:00 - 2 hours

Any idea how to do this?

Thank you in advance!

P.

[Fowmy]

@Peter_2020 

Add a calculated column using the DAX code below:

Produnction Time = 
VAR __SHIFTSTART = 6      -- Provide shift start hour 
VAR __SHIFTEND = 22       -- Provide shift end hour
VAR __START = Table1[Start]
VAR __END =  Table1[End] -  TIME(1,0,0)
VAR __SHIFTPERIOD = GENERATESERIES( __SHIFTSTART , __SHIFTEND - 1  , 1 )
VAR __PERIOD =  FILTER( GENERATESERIES( __START, __END,  TIME(1,0,0) ) , NOT WEEKDAY( [Value] , 1 ) IN {1,7} )

RETURN
    COUNTROWS(
        FILTER(
            __PERIOD,
            HOUR( [Value] ) IN  __SHIFTPERIOD
        )
    )

[Peter_2020]

@Fowmy Thank you so much! This is exactly what I needed.

But I have one more challenge - is it possible to define the breaks during the shift? Example: BREAK1 - 9:00 - 9:30, BREAK2 - 16:00 - 16:10 and that time will be of course out of the calculated production time...

I tried to add this into the formula but I don´t know how to define minutes and how will looks second part of the formula:

PRODUCTION TIME2 =

VAR __SHIFTSTART = 6

VAR __SHIFTEND = 22  

VAR __SHIFTBREAKSTART1 = 9

VAR __SHIFTBREAKEND1 = 10  

VAR __SHIFTBREAKSTART2 = 16

VAR __SHIFTBREAKEND2 = 17  

VAR __START = Table[Start date/time]

VAR __END =  Table[End date/time] -  TIME(1,0,0)

VAR __SHIFTPERIOD = GENERATESERIES( __SHIFTSTART , __SHIFTEND - 1  , 1 )

VAR __SHIFTBREAKPERIOD1 = GENERATESERIES( __SHIFTBREAKSTART1 , __SHIFTBREAKEND1 - 1  , 1 )

VAR __SHIFTBREAKPERIOD2 = GENERATESERIES( __SHIFTBREAKSTART2 , __SHIFTBREAKEND2 - 1  , 1 )

VAR __PERIOD =  FILTER( GENERATESERIES( __START, __END,  TIME(1,0,0) ) , NOT WEEKDAY( [Value] , 1 ) IN {1,7} )

RETURN

    COUNTROWS(

        FILTER(

            __PERIOD,

            HOUR( [Value] ) IN  {__SHIFTPERIOD,__SHIFTBREAKPERIOD1,__SHIFTBREAKPERIOD2}

        )

    )

Thank you for your help!

P.

[Fowmy · Accepted solution]

@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    

[Peter_2020]

 @Fowmy it works perfectly. Thank you so much for your help!

[Ahmedx]

share an example with several products