Working Hours Calculation excluding weekends and holidays

[Kushal_Chawda]

Calculating working hours is an essential measure in many business scenarios. In this post, I will demonstrate the steps to calculate this measure. Additionally, I will cover other aspects related to working hours, such as calculating overtime and excluding lunch breaks.

Note:

The example in this post assumes weekends are Saturday and Sunday. If your weekends fall on different days, please refer to the post linked below.

Working-days-and-hours-calculations-for-custom-weekends

Consider the following case:

Suppose a ticket is logged into the system on a certain day, referred to as the Start Date, and the same ticket is resolved on a different day, referred to as the End Date. We may want to calculate the working hours between these two dates to assess the efficiency of ticket closure time.

Here is how you can calculate it within the script

1) Calculate business working hours excluding week ends(Saturday and Sunday) and Holidays.

Following are the considerations

1) Count only standard working hours ( 9 AM - 6 PM) - You can change accordingly

2) Exclude Saturdays and Sundays

3) Exclude Holidays

You can adjust the date format of the variables below according to actual format in your data. Then use timestamp() function to represent it in required format.

SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss'; 

Set up the variables below for standard working hours on weekdays. You can adjust the variables according to your working hours (e.g., 9 AM - 6 PM), and the rest of the calculations will be done automatically.

// Set the start and end hour of the day in 24 hour format
LET vStartHour = 9;
LET vEndHour = 18;
LET vWorkingHourPerDay = $(vEndHour) -$(vStartHour);

Set up the holiday list as shown below. Feel free to use your own holiday list.

Holidays:
LOAD Concat(chr(39)&Holidays&chr(39),',') as Holidays Inline [
Holidays
08/03/2016
09/03/2016
17/08/2010
];

LET vHolidays = Peek('Holidays',0,'Holidays');

Following is the logic to calculate the business working hours between to dates

Data:
LOAD *,
           rangesum(
           NetWorkDays(START_TIME+1,END_TIME-1,$(vHolidays)) * MakeTime($(vWorkingHourPerDay)), // In between hours
           if(NetWorkDays(END_TIME,END_TIME,$(vHolidays)),
           Rangemin(rangemax(frac(END_TIME),maketime($(vStartHour))),maketime($(vEndHour)))-
           Rangemax(rangemin(frac(END_TIME),maketime($(vStartHour))),maketime($(vStartHour))),0), // working hours last day
           if(NetWorkDays(START_TIME,START_TIME,$(vHolidays)),
           Rangemin(rangemax(frac(START_TIME),maketime($(vEndHour))),maketime($(vEndHour)))-
           Rangemax(rangemin(frac(START_TIME),maketime($(vEndHour))),maketime($(vStartHour))),0), // working hours first day
           if(NetWorkDays(START_TIME,START_TIME,$(vHolidays)) and floor(START_TIME)=floor(END_TIME),-MakeTime($(vWorkingHourPerDay))) // If same day  then correct the hours
           )*24 AS Business_Hrs_Without_Overtime,
           
          rangesum(if(NetWorkDays(START_TIME,START_TIME,$(vHolidays)) ,
          rangesum(if(frac(START_TIME)<maketime($(vStartHour)),maketime($(vStartHour))-frac(START_TIME),0),
          if(frac(END_TIME)>maketime($(vEndHour)),frac(END_TIME)-maketime($(vEndHour)),0))*24)) as Overtime ; // Overtime

LOAD *,
           timestamp(StartTime,'DD/MM/YYYY hh:mm:ss TT') as START_TIME,
           timestamp(EndTime,'DD/MM/YYYY hh:mm:ss TT') as END_TIME
Inline [
TicketNo,StartTime, EndTime
1, 8/25/2010 3:00:00 PM, 8/27/2010 6:00:00 PM
2, 8/16/2010 10:00:00 AM, 8/17/2010 1:00:00 PM
3, 8/17/2010 1:30:00 PM, 8/17/2010 2:45:00 PM
4, 8/17/2010 3:00:00 PM, 8/18/2010 5:00:00 PM
5, 8/18/2010 5:01:00 PM, 8/19/2010 4:00:00 PM
6, 8/19/2010 5:00:00 PM, 8/20/2010 10:00:00 AM
7, 8/20/2010 11:00:00 AM, 8/20/2010 5:00:00 PM
8, 8/23/2010 2:00:00 PM, 8/23/2010 4:00:00 PM
9, 8/23/2010 5:00:00 PM, 8/23/2010 6:00:00 PM
10, 8/24/2010 7:00:00 AM, 8/24/2010 2:00:00 PM
11, 8/20/2010 5:30:00 PM,8/23/2010 1:00:00 PM
12, 3/7/2016 4:00:00 PM, 3/10/2016 6:00:00 PM
13, 8/19/2010 11:00:00 AM, 8/20/2010 6:30:00 PM];

DROP Fields StartTime, EndTime;

You can then create measures to display working hours. Use the measure below if you want to present the working hours in hh:mm:ss format.

=interval(sum(Business_Hrs_Without_Overtime)/24,'hh:mm:ss') 

2) Calculate business working hours excluding week ends(Saturday and Sunday), Lunch Breaks and Holidays.

Below are the considerations

1) Count only standard working hours ( 9 AM - 6 PM)

2) Exclude Saturdays and Sundays

3) Exclude Lunch Break (1 PM - 2PM)

4) Exclude Holidays

Set the Variables for standard working hours and lunch breaks. You can change the values according to your needs

// Set the start and end hour of the day in 24 hour format
LET vStartHour = 9;
LET vEndHour = 18;
LET vLunchStart =13;
LET vLunchEnd =14;
LET vWorkingHourPerDay = ($(vEndHour) -$(vStartHour))-($(vLunchEnd)-$(vLunchStart));

Include the Holidays

// Include the holidays list
Holidays:
LOAD Concat(chr(39)&Holidays&chr(39),',') as Holidays Inline [
Holidays
08/03/2016
09/03/2016
 ];

LET vHolidays = Peek('Holidays',0,'Holidays');

Following is the logic to calculate the business working hours between two dates

Data:            
LOAD *,
             rangesum(
             NetWorkDays(START_TIME+1,END_TIME-1,$(vHolidays)) * MakeTime($(vWorkingHourPerDay)), // 12 hours per workday, for all day inbetween the period, excluding bounderies
             if(NetWorkDays(END_TIME,END_TIME,$(vHolidays)) ,
             rangesum(rangemin(frac(END_TIME),MakeTime($(vLunchStart)))- rangemin(frac(END_TIME),MakeTime($(vStartHour))) ,
             rangemin(frac(END_TIME),MakeTime($(vEndHour))) - rangemin(frac(END_TIME),MakeTime($(vLunchEnd)))),0), // working hours last day
             if(NetWorkDays(START_TIME,START_TIME,$(vHolidays)),
             rangesum(MakeTime($(vLunchStart)) - rangemin(rangemax(frac(START_TIME), MakeTime($(vStartHour))),MakeTime($(vLunchStart))),
             MakeTime($(vEndHour)) - rangemax(rangemin(frac(START_TIME), MakeTime($(vEndHour))),MakeTime($(vLunchEnd)))),0), // working first day
             if(NetWorkDays(START_TIME,START_TIME,$(vHolidays)) and floor(START_TIME)=floor(END_TIME),-MakeTime($(vWorkingHourPerDay))) // If the same day then correct the hours
             )*24 AS Business_Hrs_Without_Overtime,
            
             rangesum(if(NetWorkDays(START_TIME,START_TIME,$(vHolidays)) ,
             rangesum(if(frac(START_TIME)<maketime($(vStartHour)),maketime($(vStartHour))-frac(START_TIME),0),
             if(frac(END_TIME)>maketime($(vEndHour)),frac(END_TIME)-maketime($(vEndHour)),0))*24)) as Overtime ; // Overtime
            
LOAD *,
            timestamp(StartTime,'MM/DD/YYYY hh:mm:ss TT') as START_TIME,
            timestamp(EndTime,'MM/DD/YYYY hh:mm:ss TT') as END_TIME;
LOAD * Inline [
TicketNo,StartTime, EndTime
1, 8/16/2010 7:00:00 AM, 8/16/2010 7:00:00 PM
2, 8/16/2010 10:00:00 AM, 8/16/2010 1:30:00 PM
3, 8/16/2010 9:00:00 AM, 8/16/2010 2:00:00 PM
4, 8/16/2010 11:00:00 AM, 8/16/2010 1:00:00 PM
5, 8/16/2010 1:15:00 PM, 8/16/2010 1:45:00 PM
6, 8/16/2010 3:00:00 PM, 8/16/2010 7:00:00 PM
7, 8/16/2010 1:30:00 PM, 8/16/2010 6:00:00 PM
8, 8/16/2010 2:00:00 PM, 8/16/2010 7:00:00 PM
9, 8/16/2010 5:00:00 PM, 8/16/2010 6:00:00 PM
10, 8/16/2010 7:00:00 AM, 8/16/2010 1:00:00 PM
11, 8/16/2010 9:30:00 AM,8/16/2010 11:00:00 AM
12, 8/16/2010 1:00:00 PM, 8/16/2010 1:34:00 PM
13, 8/16/2010 2:00:00 PM, 8/17/2010 7:00:00 PM
14, 8/16/2010 1:00:00 PM, 8/17/2010 6:00:00 PM
15, 8/16/2010 9:00:00 AM, 8/17/2010 1:00:00 PM
16, 8/16/2010 3:30:00 PM,8/17/2010 2:00:00 PM
17, 8/16/2010 7:00:00 AM, 8/17/2010 5:00:00 PM
18, 8/17/2010 10:00:00 AM, 8/19/2010 5:00:00 PM
19, 8/17/2010 3:00:00 PM, 8/19/2010 4:00:00 PM
20, 8/19/2010 1:00:00 PM, 8/24/2010 11:00:00 AM
];

Please refer to the attached applications.

Please feel free to share any suggestions.

[Anonymous]

I would like to thank kush141087‌ for the remarkable post with all these information. It is very rich and helped me a lot!

I just need a little help to understand what *24, 0.001 is doing.

The main structure of the script is like:

rangesum(

    round(

          rangesum(

          )*24, 0.001

    )

) AS Business_Hrs_Without_Overtime

I still can't understand why *24 and 0.001 are necessary and how they work along the rest of the script.

Besides, I would like to call on for some more help about the output from the outer rangesum().

What I've understood about the whole post is how "...to calculate the business working hours between two Dates".

I've came across throught some not so easy to catch on results while running the script. For example, I have two dates:

START_TIME = 23/10/2018 09:18

END_TIME = 23/10/2018 09:24

If I use these two dates on the Script, the outer rangersum() (Business_Hrs_Without_Overtime) gives me 0,11 as output.

I just don't know what I should do with this output to get, or even transform to, an Hour that make sense with the whole scope. I mean, the main ideia is to get the business working hour between two given timestamps, right? But the output from this outer rangersum() is a simple number.

If I take this number and cast a Date(), Timestamp() or Time() the result is just some random date and time.

Example:

    START_TIME = 23/10/2018 09:18

    END_TIME = 23/10/2018 09:24

    Business_Hrs_Without_Overtime = 0,11

    Timestamp(Business_Hrs_Without_Overtime) = 30/12/1899 02:38:24

Can someone help me understand how to take Business_Hrs_Without_Overtime output and transform into a correct Time?

I'll be really appreciate.

Thanks, friends.

[its_anandrjs]

Good work Kushal,

Regards,
Anand

[ambarishk]

If my holidays are going to vary from branch to branch or region to region, how it can be incorporated in the above logic.

[pitleanil3]

Good job. Its very useful ....Thanks

[Arames]

Hallo, I am new in Qlik Sense. I have one question...

How to "convert" the Minutes? for example in you first table on line 5 is "7.98" how can i get the exact minutes of 98? or also in the first table the last line 16.5 how can i get 16:30?

Thank you!

[Hakim-A]

Hello Kush,

Very interesting but 'overtime' seems not to work when we only have working hours outside of office hours.

working hours : 9-14 15-19

Example : start date 08:00:00 end date 08:39:00

Overtime should be 00:39:00 and your formula shows something else

please see this post for more details

https://community.qlik.com/t5/New-to-QlikView/working-hours-and-overtime/td-p/1626174

Thank you !

[christiandyrbye]

Just a question in terms of the loading of dates from different tables

When the StartTime and EndTime load from the same table the script works perfectly. I have the StartTime and Endtime in two different tables. The StartTime loads  but the EndTime doesn't , error message : Field 'EndTime' not found. Does this script only work when loading from one table?

LOAD *,
timestamp(timestamp#(StartTime,'MM/DD/YYYY hh:mm:ss TT')) as START_TIME,
timestamp(timestamp#(EndTime,'MM/DD/YYYY hh:mm:ss TT')) as END_TIME;

Load*,
Timestamp(Timestamp#([StartTime], 'YYYY-MM-DD hh:mm:ss TT') ) AS [StartTime]
FROM [lib://QS/FinancialTransaction.qvd](qvd);

Load*,
Timestamp(Timestamp#([EndTime], 'YYYY-MM-DD hh:mm:ss TT') ) AS [EndTime]
FROM [lib://QS/VendorJob.qvd](qvd); 

Thanks

Have a good day!

[ajayvermaida]

@Kushal_Chawda  @Sunny 

Hi Kush,

can you please help me to calculating the time diff between two dates based on given points

1- First I have to calculate the time weekdays in between 7 Am to 11 PM excluding sat and sun . am using below script for this but prob is that when both the dates are weekend then its also calculate the time diff  .

if(not WildMatch(IssueStatus,'Pending') , (Interval((RangeMin(frac(IssueResolutionDate), MakeTime(23))- RangeMax(frac(IssueCreateDate), MakeTime(7)))+
( NetWorkDays(IssueCreateDate, IssueResolutionDate-1) * MakeTime(16)) ))) as TotalTime,

2- Second I have to calculate the time diff only Weekend Sat or Sun in between 10 Am to 4 PM . also if any dates is weekend and other one is weekday then it calculate the time based on diff time difference .

3- if any ticket open in the last month and closed in next month then it calculate the time as point 1 except if it comes on weekdays .

Please help me to find the correct result 

[Claudiu_Anghelescu]

How to proceed if I have vStartHour=5, vEndHour=2 ?

[Saro_2306]

Hi All,

It would be very grateful if any one can help me to get expression  vStartHour=10.30am, vEndHour=6.30pm.

Thanks.