Calculating Working hours

I know this is already solved and just wanted to post this for others in the future.

https://www.villezekeviking.com/calculating-handling-time-during-office-hours/

This blog post explains the ways of calculating office hours using DAX and has generatic examples to copy from

any help

Phil_SeamarkZubair_Muhammad  v-yulgu-msft

Hi,

Great to see this model, I can definitively use it. But had one more question:

Any idea how to deal with varying opening hours per day of the week? (e.g. Monday 09:00-18:00, Tuesday 10:00-21:00)

Any help is much appreciated!

try this simple solution:

//New Column

Working Hour = DATEDIFF ( [ACTIVITY_DATE], [LASTMODIFIEDDATE], HOUR )

I know this is a bit of an older thread, but needed to implement this and found that there were some errors/inconsistencies in the accepted solution, so I thought I'd post my modified code in the event that anyone else needs it in the future:

FR_WorkHour = 

VAR Start_Date = 'SQL: Tickets'[created_at] //set the start date/time variable
VAR End_Date = if('SQL: Tickets'[first_responded_at] <> BLANK(),'SQL: Tickets'[first_responded_at],if('SQL: Tickets'[resolved_at] <> BLANK(), 'SQL: Tickets'[resolved_at],NOw())) // set the end date/time variable


/* Create a dynamic calendar from the start and end dates, and create a cross join table with the available working hours */
VAR workcal =
    FILTER (
        ADDCOLUMNS (
            CROSSJOIN (
                CALENDAR ( DATEVALUE(Start_Date), DATEVALUE(End_Date) ),
                SELECTCOLUMNS ( GENERATESERIES ( 8, 17 ), "Hour", [Value] ) // set the work hours here. If you need to include a break, change the generate series to an array of the start hours e.g. [9,10,11,13,14,15...]
            ),
            "Day of week", WEEKDAY ( [Date], 2 )
        ),
        [Day of week] < 6
    )

/* Count the number of whole hours between the datetimes */
VAR hourcount = 
    COUNTROWS(
        FILTER (workcal,
            (
            ([Date] = DATEVALUE( Start_Date )
                && 
                [Hour] > HOUR( Start_Date ))
            && 
            ([Date] = DATEVALUE( End_Date ) 
                && 
                [Hour] <= HOUR( End_Date ))
            )
            ||
            ([Date] > Start_Date  && [Date] < End_Date )
        )
    )

/* Determine how much extra time (in minutes) is outside of the whole hours (hourcount) */
VAR remained = 
    if(
        DATEVALUE(Start_Date) = DATEVALUE(End_Date) && HOUR(Start_Date) = HOUR(End_Date), 
            DATEDIFF(Start_Date,End_Date,MINUTE),
            DATEDIFF (
                TIMEVALUE ( Start_Date ),
                TIME ( HOUR ( Start_Date ) + 1, 0, 0 ),
                MINUTE
            )
            + 
            DATEDIFF (
                TIME ( HOUR ( End_Date ) - 1, 0, 0 ),
                TIMEVALUE ( End_Date ),
                MINUTE
            )
    )

VAR worktime = (hourcount*60 + remained)/60

RETURN
    worktime

Hi, I worked on this new solution, i hope it can usefull for you.

Any questions you can contact me.

Regards!

Tiempo Minutes = 
VAR WrongDate = IF([Fecha y hora de correo]>[Fecha y hora Primera llamada],TRUE(),FALSE())
VAR SDate_1 = MIN([Fecha y hora de correo],[Fecha y hora Primera llamada])
VAR FDate_1 = MAX([Fecha y hora de correo],[Fecha y hora Primera llamada])
VAR SDate_2 = IF(TIMEVALUE(SDate_1) < TIME(8,30,0),DATEVALUE(SDate_1)+TIME(8,30,0)
                    ,IF(TIMEVALUE(SDate_1) > TIME(17,30,0),DATEVALUE(SDate_1)+TIME(17,30,0)
                        ,SDate_1)) - (30/60/24)
VAR FDate_2 = IF(TIMEVALUE(FDate_1) < TIME(8,30,0),DATEVALUE(FDate_1)+TIME(8,30,0)
                    ,IF(TIMEVALUE(FDate_1) > TIME(17,30,0),DATEVALUE(FDate_1)+TIME(17,30,0)
                        ,FDate_1)) - (30/60/24)

VAR SDate_3 = IF(WEEKDAY(SDate_2,2)=6,DATEVALUE(SDate_2)-(WEEKDAY(SDate_2,2)-5)+(17/24),SDate_2)
VAR FDate_3 = IF(WEEKDAY(FDate_2,2)=6,DATEVALUE(FDate_2)-(WEEKDAY(FDate_2,2)-5)+(17/24),FDate_2)

VAR Calendar_1 = FILTER(ADDCOLUMNS(CROSSJOIN(CALENDAR(SDate_3,FDate_3),SELECTCOLUMNS(GENERATESERIES(8, 17),"Hour",[Value]))
                                  ,"DOW",WEEKDAY([Date],2),"Date_Time",[Date]+[Hour]/24),[DOW] < 6)
VAR Calendar_2_1 = FILTER(ADDCOLUMNS(Calendar_1,"Val_Inicio",IF([Date_Time]>=SDate_3 && [Date_Time]<=FDate_3,1,0)),[Val_Inicio] = 1)
VAR Calendar_2_2 = SELECTCOLUMNS(Calendar_2_1,"Fecha",[Date],"FechaTiempo",[Date_Time])

VAR Calendar_3 = SUMMARIZE(Calendar_2_1,[Date],"Min_DateTime",MINX(FILTER(Calendar_2_2,[Fecha] = [Date]),[FechaTiempo])
                                            ,"Max_DateTime",MAXX(FILTER(Calendar_2_2,[Fecha] = [Date]),[FechaTiempo]))
VAR Calendar_4 = ADDCOLUMNS(Calendar_3,"Date_Start",IF(DATEVALUE([Min_DateTime]) = DATEVALUE(SDate_3),MIN([Min_DateTime],SDate_3),[Min_DateTime])
                                      ,"Date_End",IF(DATEVALUE([Max_DateTime]) = DATEVALUE(FDate_3),MAX([Max_DateTime],FDate_3),[Max_DateTime]))
VAR Time = SUMX(ADDCOLUMNS(Calendar_4,"Minutes",DATEDIFF([Date_Start],[Date_End],MINUTE)),[Minutes])
VAR Minutes = IF(ISBLANK(Time),DATEDIFF(SDate_1,FDate_1,MINUTE),Time)
RETURN
IF(WrongDate,Minutes*-1,Minutes)

Hi

Please can you advise .. I have tried using the scripts to calculate business hours ... its not even close ... I am pretty sure I am doing it correctly but clearly not 🙂 

Please can you help

thanks

Marc

Bring some new life to an old thread.

I was staring at this issue with no idea where to start, and then I stumble on this thread which gave me hope.

Unfortunately the examples in this thread gave wildly wrong figures and missed a couple of edge cases that may data was riddled with.

So with massive inspiration from this thread I have put the below together which as far as I can tell is spot on for my 300,000+ data set:

Business Hours = 

VAR BusinessStart = 8
VAR BusinessEnd = 18
VAR StartDate = DATEVALUE ( [Start] )
VAR StartHour = HOUR ( [Start] )
VAR EndDate = DATEVALUE ( [End] )
VAR EndHour = HOUR ( [End] )

//Generate a table of Dates and Hours based on the Start and End of item
VAR BusinessCalendar =
    FILTER (
        ADDCOLUMNS (
            CROSSJOIN (
                CALENDAR ( StartDate, EndDate ),
                SELECTCOLUMNS ( GENERATESERIES ( BusinessStart, BusinessEnd ), "Hour", [Value] )
            ),
            "Day of week", WEEKDAY ( [Date], 2 )
        ),
        [Day of week] < 6
    )


//Calculate the number of hours on the first day
//Also check if the start and end dates are the same and account for that, will drop lastDayHours in final calc
VAR firstDayHours = 
	IF ( StartDate <> EndDate,

		COUNTROWS (
			FILTER (
				BusinessCalendar,
				([Date] = StartDate && [HOUR] > StartHour )
				)
			),
			
		COUNTROWS (
			FILTER (
				BusinessCalendar,
				([Date] = StartDate && [HOUR] > StartHour && [HOUR] < EndHour )
				)
			)
			
		)

//Calculate hours in last day
VAR lastDayHours = 
	COUNTROWS (
		FILTER (
			BusinessCalendar,
			([Date] = EndDate && [HOUR] < EndHour )
			)
		)
		
//Add first and last hours together unless both same day
VAR combinedHours = 
	IF ( StartDate = EndDate,
		firstDayHours,
		firstDayHours + lastDayHours
	)

//Calculate the hours from the full days
VAR fullDays = 
	COUNTROWS (
		FILTER (
			BusinessCalendar,
			([Date] > StartDate && [Date] < EndDate)
		)
	)


//Calculate the minutes in the hours at each end of the date/time period
//Check if start and end hours are the same and account for that
VAR remainingMinutes = 
	IF ( StartDate <> EndDate,
		(60 - MINUTE ( [Start] )) + MINUTE ( [End] ),
		IF ( StartHour <> EndHour,
			(60 - MINUTE ( [Start] )) + MINUTE ( [End] ),
			MINUTE ( [End] ) - MINUTE ( [Start] )
		)
	)
			
	
RETURN
	( ( (combinedHours + fullDays ) * 60 ) + remainingMinutes ) / 60

NOTE - This does not account for End dates that are after the Start date, these will be calculated as negative values, this was not an issue for my data set but this could be handled with the MIN function to clamp to 0