Calculating Working hours

[rocky09]

I have this following Data, I am trying to find a way to calculating  Working hours in betwen dates excluding Weekends.

Works hours are between: Morning 9:00 AM to Evening 6:00 PM and Saturday and Sunday are holidays.

TicketID	ACTIVITY_DATE	LASTMODIFIEDDATE
86256	28-12-2017 03:11	28-12-2017 15:11
89890	15-08-2017 20:23	15-08-2017 21:12
111611	04-10-2017 21:30	10-10-2017 13:00
111511	04-10-2017 02:30	10-10-2017 13:00
111542	04-10-2017 02:41	10-10-2017 13:00
111485	04-10-2017 02:41	10-10-2017 13:00
211411	06-10-2017 14:25	10-10-2017 13:00
150895	10-11-2017 12:32	18-01-2018 13:29
152996	08-08-2017 03:40	09-08-2017 11:00

any help?

[Anonymous]

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

[ashleybaldwin]

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

[Anonymous]

This is great! How would I go about excluding holidays as well? 

[MarcUrdang]

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

[Bruno_Camacho]

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)

[MarcelWoodman]

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

[PBI_newuser]

Hi @MarcelWoodman ,

There is an error message "An argument of function 'TIME' has the wrong data type or the result is too large or too small."

I have changed the data type for [Start_Date] and [End_Date] to Date/Time.

Please help. Thank you.

[MarcelWoodman]

@PBI_newuser, If I had to guess, I think it would have to be in the last function where we subtract 1 from the hour. If the time that the ticket is closed is at the midnight hour (0:00) then it could return -1, which is an invalid time.

Try throwing an if statment in to say if hour = 0, then 23, else -1. Something like this:

DATEDIFF (
                TIME ( IF ( HOUR ( End_Date ) = 0, 23, HOUR ( End_Date ) - 1), 0, 0 ),
                TIMEVALUE ( End_Date ),
                MINUTE
            )

[PBI_newuser]

Thanks @MarcelWoodman ! It works now but the work hours is incorrect.

Below is the column i created. Is there any error? Please help. Thanks.

Work Hour = 
VAR workcal =
 FILTER (
 ADDCOLUMNS (
 CROSSJOIN (
 CALENDAR ( DATEVALUE([Start Date]), DATEVALUE([End Date]) ),
 SELECTCOLUMNS ( GENERATESERIES ( 8, 17 ), "Hour", [Value] )
 ),
 "Day of week", WEEKDAY ( [Date], 2 )
 ),
 [Day of week] < 6
 )
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] )
 )
 )
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 ( IF ( HOUR ([End Date] ) = 0, 23, HOUR ([End Date] ) - 1), 0, 0 ),
 TIMEVALUE ( [End Date] ),
 MINUTE
 )
 )
VAR worktime = (hourcount*60 + remained)/60
RETURN
 worktime

[MarcelWoodman]

@PBI_newuser, so my previous code was terrible 😁

Not sure if this completely works, but it does seem at least *more* accurate:

BusinessHours = 
VAR Start_Date = [DateTime]
VAR End_Date = [DateTime]
VAR Start_Hour = 8
VAR End_Hour = 17
VAR workcal =
    FILTER (
        ADDCOLUMNS (
            CROSSJOIN (
                CALENDAR ( DATEVALUE(Start_Date), DATEVALUE(End_Date) ),
                SELECTCOLUMNS ( GENERATESERIES ( Start_Hour, End_Hour ), "Hour", [Value] )
            ),
            "Day of week", WEEKDAY ( [Date], 2 )
        ),
        [Day of week] < 6
    )
VAR starthourcount = 
    COUNTROWS(
        FILTER (workcal,
            (
            [Date] = DATEVALUE(Start_Date)
                && 
                [Hour] > HOUR(Start_Date)
            )
        )
    )
VAR endhourcount = 
    COUNTROWS(
        FILTER (workcal,
            (
            [Date] = DATEVALUE(End_Date) 
                && 
                [Hour] < HOUR(End_Date)
            )
        )
    )
VAR daycount = 
    COUNTROWS(
        FILTER (workcal,
            ([Date] > DATEVALUE(Start_Date) && [Date] < DATEVALUE(End_Date) )
        )
    )
VAR remained = 
    if(
        DATEVALUE(Start_Date) = DATEVALUE(End_Date) && HOUR(Start_Date) = HOUR(End_Date), 
            DATEDIFF(Start_Date,End_Date,MINUTE),
            IF(
				HOUR (Start_Date) < Start_Hour || HOUR (Start_Date) >= End_Hour, 0,
				DATEDIFF (
					TIMEVALUE ( Start_Date ),
					TIME ( HOUR ( Start_Date ) + 1, 0, 0 ),
					MINUTE
				)
			)
            +
			IF(
				HOUR (End_Date) < Start_Hour || HOUR (End_Date) >= End_Hour, 0,
				DATEDIFF (
					TIME ( HOUR ( End_Date ), 0, 0 ),
					TIMEVALUE ( End_Date ),
					MINUTE
				)
			)
    )
VAR worktime = ((daycount + starthourcount + endhourcount)*60 + remained)/60
RETURN
    worktime

[alevandenes]

Hi @MarcelWoodman 

do you have a formula to remove the cases in which start date>end date??

I would like to have these dates out of the calculation

Thanks a lot in advance!
Alessandra

[Anonymous]

Thanks @MarcelWoodman your solution gave me a great start to my own particular problem of a business hours calculation for two columns that are a ticket opening date/time and a ticket response date/time. 

A noticable problem to overcome was the effect of the calculation when open and response happen on the same day compared to when the response is the next day or later. To solve this I had to create 3 new columns altogether, one that is suited to a same day response, one that is suited to a next day or later response and one for the required result of business response duration (measured in minutes in my case) :

sameday_response_minutes = 
/* This calculates the number of busines minutes between ticket open and being accepted when both occur on the same day */

VAR Start_Date = 'Data Table'[opened]
VAR End_Date = 'Data Table'[responded]
VAR Start_Hour = 8
VAR End_Hour = 17

/* calculate the number of full business hours on the day */

VAR first_day_hours_cal = 
	FILTER (
		ADDCOLUMNS (
			GENERATESERIES ( Start_Hour, End_Hour ), "Hour", [Value]), ((hour(End_Date)- [Hour])>0 && hour(End_Date)>hour(Start_Date) && hour(Start_Date)<[Hour]  ))
			

VAR first_day_hours = COUNTROWS(first_day_hours_cal)

/* calculate the number of full business minutes of the first hour */

VAR first_day_minutes = 60 - MINUTE(Start_Date)

/* calculate the number of full business seconds of the first hour */
VAR first_day_seconds = (60 - SECOND(Start_Date))/60

/* calculate the number of full business minutes of the last hour */
VAR last_day_minutes = if(hour(End_Date)>Start_Hour && HOUR(End_Date)<End_Hour,MINUTE(End_Date),0)
/* calculate the number of full business seconds of the last hour */
VAR last_day_seconds = if(hour(End_Date)>Start_Hour && HOUR(End_Date)<End_Hour,SECOND(End_Date),0)

/* add them all up to get the response time in minutes under different conditions 1. when the response is at the same moment as the ticket is opened, 2. when the ticket is opened and responded out of business hours, 3. at the weekend, 4. when the start and response are both during business hours, 5. when ticket is opened or responded outside of business hours */

VAR response_time=if(Start_Date=End_Date,0,if(HOUR(Start_Date)>End_Hour && HOUR(End_Date)>End_Hour,0,(if(WEEKDAY(Start_date,2)>5,0,if((hour(Start_Date)>Start_Hour && hour(End_Date)<End_Hour),((time(hour(End_Date),MINUTE(End_Date),SECOND(End_Date))-TIME(HOUR(Start_Date),MINUTE(Start_Date),second(Start_Date)))*(24*60)),( (first_day_hours*60) + first_day_minutes - first_day_seconds + last_day_minutes + last_day_seconds))))))

RETURN
    response_time

slow_response_minutes = 
/* this calculates the business minutes between ticket opening and ticket being accepted when the response happens on the next day or later */

VAR Start_Date = 'Data Table'[opened]
VAR End_Date = 'Data Table'[responded]
VAR Start_Hour = 8
VAR End_Hour = 17

/* calculate the number of full business days between open and response */
VAR full_days_table =
    FILTER (
        ADDCOLUMNS (

                CALENDAR ( Start_Date, End_Date), "Day of week", WEEKDAY ( [Date], 2 ) 
        ),
        [Day of week] < 6
    )   
VAR full_daycount = 
    COUNTROWS(
        FILTER (full_days_table,
            ([Date] > DATEVALUE(Start_Date) && [Date] < DATEVALUE(End_Date) )
        )
    )
	
/* calculate the number of full business hours on the start day */

VAR first_day_hours_table = 
	FILTER (
		ADDCOLUMNS (
			GENERATESERIES ( Start_Hour, End_Hour ), "Hour", [Value]), (hour(Start_Date) < [Hour] && hour(Start_Date)>=Start_Hour) )
			

VAR first_day_hours = COUNTROWS(first_day_hours_table)

/* calculate the number of full business minutes on the start day */

VAR first_day_minutes = 60 - MINUTE(Start_Date)

/* calculate the number of full business seconds on the start day */
VAR first_day_seconds = (60 - SECOND(Start_Date))/60

/* calculate the number of full business hours on the end day */
VAR last_day_hours_cal = 
	FILTER (
		ADDCOLUMNS (
			GENERATESERIES ( Start_Hour, End_Hour ), "Hour", [Value]), (hour(End_Date) > [Hour] && hour(End_Date)<=End_Hour) )
			

VAR last_day_hours = COUNTROWS(last_day_hours_cal)

/* calculate the number of full business minutes on the end day */
VAR last_day_minutes = if(hour(End_Date)>Start_Hour && HOUR(End_Date)<End_Hour,MINUTE(End_Date),0)
/* calculate the number of full business seconds on the end day */
VAR last_day_seconds = if(hour(End_Date)>Start_Hour && HOUR(End_Date)<End_Hour,SECOND(End_Date),0)



/* add them all up to get the response time in minutes */

VAR response_time =  (full_daycount*(End_Hour-Start_Hour)*60) + (first_day_hours*60) + first_day_minutes - first_day_seconds + (last_day_hours*60) + last_day_minutes + last_day_seconds


RETURN
    response_time

response_time_minutes = if(DATEDIFF('Data Table'[opened],'Data Table'[responded],DAY)=0,'Data Table'[sameday_response_minutes],'Data Table'[slow_response_minutes])

So far I haven't found any mathematical errors but when the report is implemented I'll monitor it for any scenarios that haven't been accounted for.

[PBI_newuser]

@MarcelWoodman Thanks a lot for your help! But it still doesn't work for me. 

[MarcelWoodman]

@PBI_newuser, so honestly, I'm not sure. I ran into a lot of similar issues when coming up with that code, because there are a number of exceptions that need to be accounted for.

One thing that I did to help was break the functions up piecemeal so that I could see where the errors were coming from, and then worked the code back into a single function. I think I'm going to have to go back and check my data to see if it's also pulling the wrong hour count.

[Netjacker65]

try this simple solution:

//New Column

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

[Anonymous]

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!

[v-shex-msft · Accepted solution]

HI @rocky09,

You can try to use below calculated column formula to calculate valid working hour:

Work Hour = 
VAR filtered =
    FILTER (
        ADDCOLUMNS (
            CROSSJOIN (
                CALENDAR ( [ACTIVITY_DATE], [LASTMODIFIEDDATE] ),
                SELECTCOLUMNS ( GENERATESERIES ( 9, 18 ), "Hour", [Value] )
            ),
            "Day of week", WEEKDAY ( [Date], 2 )
        ),
        [Day of week] < 6
            && [TicketID] = EARLIER ( Table1[TicketID] )
    )
VAR hourcount =
    COUNTROWS (
        FILTER (
            filtered,
            (
                [Date] >= DATEVALUE ( [ACTIVITY_DATE] )
                    && [Hour]
                        > HOUR ( [ACTIVITY_DATE] ) + 1
            )
                && (
                    [Date] <= DATEVALUE ( [LASTMODIFIEDDATE] )
                        && [Hour]
                            > HOUR ( [LASTMODIFIEDDATE] ) - 1
                )
        )
    )
VAR remained =
    DATEDIFF (
        TIMEVALUE ( [ACTIVITY_DATE] ),
        TIME ( HOUR ( [ACTIVITY_DATE] ) + 1, 0, 0 ),
        MINUTE
    )
        + DATEDIFF (
            TIME ( HOUR ( [LASTMODIFIEDDATE] ) - 1, 0, 0 ),
            TIMEVALUE ( [LASTMODIFIEDDATE] ),
            MINUTE
        )
RETURN
    IF ( hourcount <> BLANK (), (hourcount*60 + remained)/60, 0 )

 

Regards,

Xiaoxin Sheng

[Anonymous]

Hi

I am also getting the "The start date in Calendar function can not be later than the end date" error

but when I change my workiung hours this way, as in the code section I do not get the error BUT obviously have 0's as that is not working time

SELECTCOLUMNS ( GENERATESERIES (17,6 ), "Hour", [Value] )

[Anonymous]

@v-shex-msft

I was looking at your code, and I think it is probably going to be pretty useful for what I'm doing.

I was wondering if it would be possible to consider more than one "range" of working hours (I want to take out the lunch time) without having to create multiple columns and then adding them, just to make the code cleaner.

In my case, the time range would be like:

- 8:00 - 12:00

- 13:00 - 17:15

Thank you in advance!

[Anonymous]

I am getting the following error. Any idea how to fix it? 

"An argument of function 'TIME' has the wrong data type or the result is too large or too small."