Reply
Topic Options
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Response Time (exclude lunch-break, weekends, holidays)
11-21-2022
12:56 AM
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 |
1 REPLY 1
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
11-21-2022
01:37 AM

Helpful resources
Featured Topics
Top Solution Authors (Last Month)
User | Count |
---|---|
59 | |
59 | |
56 | |
38 | |
29 |
Top Kudoed Authors (Last Month)
User | Count |
---|---|
78 | |
62 | |
45 | |
40 | |
39 |