measure 2 different work hours time and excluding official holidays

[shado26]

HI Folks
need your help to improve the below formula to measure 2 different work hours in specified month if i working hours from 9 AM to 3 PM  and to excluding public holiday

ifmet = 
VAR sla2 =
    DATEDIFF ( Time[start], Time[end], SECOND )
RETURN
    IF (
        WEEKDAY ( Time[start],2 ) IN { 5, 6 },
        "done",
        IF (
            TIMEVALUE ( Time[start] ) < TIME ( 9, 0, 0 )
                || TIMEVALUE ( Time[end] ) > TIME ( 18, 0, 0 ),
            "done",
            IF ( sla2 <= 5400, "done", IF(Value[Value]>=5000,"done","Failed" )
        )
    ))

small hits : just fourmal are measured normal working time and excluding weekends only

thanks in advanced 

[v-ljerr-msft · Accepted solution]

Hi @shado26,

To exclude the public holidays in your formula, a holiday table including all holidays may be needed to be added to your model. Then you should be able to follow steps in this thread to add the [If work day] column in your time table.

If work day = IF(OR(DateTable[WeekDay]=1,DateTable[WeekDay]=7),0,IF(ISBLANK(DateTable[Holiday]),1,0))

And add it to your formula like below. 

ifmet = 
VAR sla2 =
    DATEDIFF ( Time[start], Time[end], SECOND )
RETURN
    IF (
        Time[If work day]<>1,
        "done",
        IF (
            TIMEVALUE ( Time[start] ) < TIME ( 9, 0, 0 )
                || TIMEVALUE ( Time[end] ) > TIME ( 18, 0, 0 ),
            "done",
            IF ( sla2 <= 5400, "done", IF(Value[Value]>=5000,"done","Failed" )
        )
    ))

Regards