hi,
with my solution you can calculate Business hours between two dates:
- Take out the holidays
- Determine your beginning and end of working days
- and ...
for use:
- use a master calendar and defined Business hours start and end, flag holidays and ...
| M_Date | WeekDay | BusStartTime | BusEndTime | WorkTime | IsHoliday |
| 2021/03/21 | 2 | 00:00 | 00:00 | 00:00 | 1 |
| 2021/03/22 | 3 | 00:00 | 00:00 | 00:00 | 1 |
| 2021/03/23 | 4 | 00:00 | 00:00 | 00:00 | 1 |
| 2021/03/24 | 5 | 00:00 | 00:00 | 00:00 | 1 |
| 2021/03/25 | 6 | 08:00 | 12:00 | 04:00 | 0 |
| 2021/03/26 | 7 | 00:00 | 00:00 | 00:00 | 1 |
| 2021/03/27 | 1 | 08:00 | 16:30 | 08:30 | 0 |
| 2021/03/28 | 2 | 08:00 | 16:30 | 08:30 | 0 |
| 2021/03/29 | 3 | 00:00 | 00:00 | 00:00 | 1 |
| 2021/03/30 | 4 | 08:00 | 16:30 | 08:30 | 0 |
| 2021/03/31 | 5 | 08:00 | 16:30 | 08:30 | 0 |
| 2021/04/01 | 6 | 00:00 | 00:00 | 00:00 | 1 |
| 2021/04/02 | 7 | 00:00 | 00:00 | 00:00 | 1 |
| 2021/04/03 | 1 | 08:00 | 16:30 | 08:30 | 0 |
| 2021/04/04 | 2 | 08:00 | 16:30 | 08:30 | 0 |
| 2021/04/05 | 3 | 08:00 | 16:30 | 08:30 | 0 |
| 2021/04/06 | 4 | 08:00 | 16:30 | 08:30 | 0 |
| 2021/04/07 | 5 | 08:00 | 16:30 | 08:30 | 0 |
| 2021/04/08 | 6 | 08:00 | 12:00 | 04:00 | 0 |
| 2021/04/09 | 7 | 00:00 | 00:00 | 00:00 | 1 |
| 2021/04/10 | 1 | 08:00 | 16:30 | 08:30 | 0 |
| 2021/04/11 | 2 | 08:00 | 16:30 | 08:30 | 0 |
| 2021/04/12 | 3 | 08:00 | 16:30 | 08:30 | 0 |
| 2021/04/13 | 4 | 08:00 | 16:30 | 08:30 | 0 |
| 2021/04/14 | 5 | 08:00 | 16:30 | 08:30 | 0 |
| 2021/04/15 | 6 | 08:00 | 12:00 | 04:00 | 0 |
| 2021/04/16 | 7 | 00:00 | 00:00 | 00:00 | 1 |
| 2021/04/17 | 1 | 08:00 | 16:30 | 08:30 | 0 |
| 2021/04/18 | 2 | 08:00 | 16:30 | 08:30 | 0 |
| 2021/04/19 | 3 | 08:00 | 16:30 | 08:30 | 0 |
| 2021/04/20 | 4 | 08:00 | 16:30 | 08:30 | 0 |
- use subroutine and load data:
Sub BusinessWorkTime(vTableName, vCaseId, vStartTime, vEndTime)
Calendar: LOAD M_Date, BusStartTime, BusEndTime, WorkTime
FROM [.\Calendar.xlsx](ooxml, embedded labels, table is Cal);
tmp1:
NoConcatenate LOAD
[$(vCaseId)] as CaseId
,[$(vStartTime)] as Start
,[$(vEndTime)] as End
,Time(Frac([$(vStartTime)])) as StartTime
,Time(Frac([$(vEndTime)])) as EndTime
Resident [$(vTableName)];
Left Join IntervalMatch ( M_Date )
LOAD Floor(Start), End Resident tmp1;
Left Join (tmp1) LOAD * Resident Calendar;
tmp2:
NoConcatenate LOAD *,
Time(if( M_Date > Floor(Start) and M_Date <> Floor(End) , WorkTime,
if( M_Date = Floor(Start) and M_Date <> Floor(End) , if(StartTime > BusEndTime, 0 , BusEndTime-RangeFractile(0.5,StartTime,BusStartTime,BusEndTime)),
if( M_Date = Floor(End) and M_Date <> Floor(Start) , if(EndTime < BusStartTime, 0 , RangeMin(EndTime,BusEndTime)-BusStartTime),
if( M_Date = Floor(End) and M_Date = Floor(Start) , RangeMin(EndTime,BusEndTime)-RangeFractile(0.5,StartTime,BusStartTime,BusEndTime),
'NA' ))))) as WorkTimeAdj
Resident tmp1;
Left Join ([$(vTableName)])
LOAD
CaseId as [$(vCaseId)],
Interval(Sum(WorkTimeAdj)) as BusinessCycleTime
Resident tmp2
Group by CaseId;
DROP Tables Calendar,tmp1,tmp2;
End Sub
//==============================================
Data:
LOAD id ,
Created,
Resolved,
Interval(Resolved-Created) as CycleTime
FROM [.\Ticket.xlsx](ooxml, embedded labels, table is Sheet1);
Call BusinessWorkTime('Data','id','Created','Resolved');
and check result:
| id | Created | Resolved | CycleTime | BusinessCycleTime |
| 12976446 | 2021/04/11 10:13:09 | 2021/04/11 10:23:19 | 0:10:10 | 0:10:10 |
| 12975880 | 2021/04/11 08:31:36 | 2021/04/11 12:48:26 | 4:16:50 | 4:16:50 |
| 12974323 | 2021/04/10 18:34:59 | 2021/04/10 18:48:22 | 0:13:23 | 0:00:00 |
| 12974191 | 2021/04/10 17:59:19 | 2021/04/10 19:02:26 | 1:03:07 | 0:00:00 |
| 12971459 | 2021/04/10 10:57:36 | 2021/04/10 18:00:14 | 7:02:38 | 5:32:24 |
| 12971013 | 2021/04/10 09:47:06 | 2021/04/11 07:40:10 | 21:53:04 | 6:42:54 |
Does anyone have a better idea for calculating Business hours between two dates?
