cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## Response Time (exclude lunch-break, weekends, holidays)

Hi

I have two date fields with times. I want to calculate the response time of mins between Start and Finish excluding lunch breaks, weekends, and holidays.

I found the formula to exclude weekends, and holidays but how to exclude lunch also

Response time (SLA) =
VAR _M=CALCULATE(COUNTROWS('Date'),DATESBETWEEN('Date'[Date],'Table'[StartDate]+1,'Table'[ClosedDate]-1),'Date'[Weekday1]<> 6&&'Date'[Weekday1]<>7,ALL('Table'))*7.5*60
VAR _S=IF(WEEKDAY('Table'[StartDate],2)IN{6,7},0,IF(HOUR('Table'[StartDate])<9,7.5*60,DATEDIFF('Table'[StartDate],DATE(YEAR('Table'[StartDate]),MONTH('Table'[StartDate]),DAY('Table'[StartDate]))+TIME(17,00,00),MINUTE)))
VAR _C=IF(WEEKDAY('Table'[ClosedDate],2)IN{6,7},0,IF(HOUR('Table'[ClosedDate])>=17,7.5,DATEDIFF(DATE(YEAR('Table'[ClosedDate]),MONTH('Table'[ClosedDate]),DAY('Table'[ClosedDate]))+TIME(9,00,00),'Table'[ClosedDate],MINUTE)))
VAR _Minute=IF(ISBLANK('Table'[ClosedDate]),BLANK(),_M+IF(EDATE('Table'[StartDate],0)=EDATE('Table'[ClosedDate],0)&&NOT(WEEKDAY('Table'[StartDate],2)IN{6,7}),DATEDIFF('Table'[StartDate],'Table'[ClosedDate],MINUTE),_S+_C))
RETURN
IF(_Minute=BLANK(),BLANK(),_Minute)

Lunch Hour 13:00 to 14:00

 25-10-22 10:02 31-10-22 16:58 31-10-22 14:40 31-10-22 16:48 31-10-22 12:17 31-10-22 16:50 31-10-22 15:03 31-10-22 17:00 31-10-22 15:45 31-10-22 16:44 31-10-22 14:19 31-10-22 16:44