cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dun713
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:0231-10-22 16:58
31-10-22 14:4031-10-22 16:48
31-10-22 12:1731-10-22 16:50
31-10-22 15:0331-10-22 17:00
31-10-22 15:4531-10-22 16:44
31-10-22 14:1931-10-22 16:44

 

1 REPLY 1

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors