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

Bruno_Camacho's avatar
Bruno_Camacho
New Member
6 years ago

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)