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
amitchandak
Super User
Super User

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors