Forum Discussion

rocky09's avatar
rocky09
Solution Sage
8 years ago
Solved

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's avatar
    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's avatar
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
PBI_newuser's avatar
PBI_newuser
Post Prodigy
6 years ago

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