Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Sep 26, 2024 8:19:37 AM
Apr 23, 2016 7:32:24 AM
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.
.png "Luminary Alumni"){kind=icon}
.png "prasad_dumbre"){kind=avatar}
