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

PBI_newuser's avatar
PBI_newuser
Post Prodigy
6 years ago

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's avatar
MarcelWoodman
Regular Visitor
6 years ago

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.