Calculating Working hours

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 S...

Need Help

Anonymous

8 years ago

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

MarcelWoodman

Regular Visitor

6 years ago

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

Anonymous

Not applicable

6 years ago

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.

Forum Discussion

Calculating Working hours

Related content

Hour Calculation

Calculating hours worked

Calculating Network Hours / Working Hours

Calculate working hours between two dates

Calculating Working Hours by Month