Calculate Business hours between two dates

j_nourbakhsh — Mon, 26 Jan 2026 16:26:21 GMT

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?

Re: Calculate Business hours between two dates

yilmazmurat — Mon, 12 Apr 2021 11:02:49 GMT

Check the link below 🙂

https://community.qlik.com/t5/QlikView-Documents/Working-Hours-Calculation-excluding-weekends-and-holidays/ta-p/1477884

topic Calculate Business hours between two dates in QlikView

Calculate Business hours between two dates

Re: Calculate Business hours between two dates