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

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

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
            )