Skip to main content
Announcements
Document boards are being consolidated, this board no longer allows NEW documents READ MORE

Working Hours Calculation excluding weekends and holidays

cancel
Showing results for 
Search instead for 
Did you mean: 
Kushal_Chawda

Working Hours Calculation excluding weekends and holidays

Last Update:

Sep 21, 2022 1:04:33 PM

Updated By:

Sue_Macaluso

Created date:

Apr 23, 2016 7:32:24 AM

Attachments

Working hours calculation is essential measure which is useful in many business cases.  In this post I am going to demonstrate the steps to calculate this measure.  I have also covered other aspects related to working hours like overtime hours and excluding lunch break hours.  

Example in this post works for weekends as Saturday and Sunday. If you have any other weekends then refer to my below post

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

Consider the below case

Let's say one Ticket is logged into the system on certain day , call it as Start Date, and the same Ticket got resolved on certain day, call it as End Day. Now we may want to calculate the working hours between these two Dates to calculate the efficiency of ticket closure time.

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

Below are the considerations

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

2) Exclude Saturdays and Sundays

3) Exclude Holidays

Below is the Setup in QlikView

You can change the Date format of the below variables as per your convenience eg. 'DD/MM/YYYY hh:mm:ss' to 'DD-MMM-YYYY hh:mm:ss'

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

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

First, convert your Timestamp date format to 24 hour format using the below functions

a) Timestamp# ()    (if your Timestamp values are text) 

b) TimeStamp ()     (If your Timestamp values are in proper Timestamp format)

Now you can setup the below variables for standard working hours in weekdays.You can change the Variable according to your working hours (Here 9 AM - 6 PM) and rest calculation will be done automatically

Now Setup the Holiday list as below. You can use your own holiday list

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

Here is the output

 

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

Include the Holidays

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

 

 

Here is the output

 

If you want to present the working hours in hh:mm:ss format then you can use below logic after calculating working hours

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

Please see the attached QVW.

Feel free to provide any suggestions.

Tags (1)
Comments
Anonymous
Not applicable

nice

Anonymous
Not applicable

Thanks Kushal for sharing this...i really needed this one. Great Work

0 Likes
danansell42
Creator III
Creator III

Great post and very useful.

Is there anyway to do something similar based on a 6 day working week (Sunday being the off day)?

0 Likes
Anonymous
Not applicable

Its really useful, i used your code in a variable function Business hours function

punitpopli
Specialist
Specialist

Great Post!!

Regards,

Punit

0 Likes
Not applicable

Very Helpfull

Regards

Kiran

0 Likes
ahaahaaha
Partner - Master
Partner - Master

Thanks, useful material.

Regards,

Andrey

0 Likes
cotiso_hanganu
Partner - Creator III
Partner - Creator III

Cool ! Tx

0 Likes
dathathreya
Creator
Creator

usefull post,Thanks for sharing this Kushal.

0 Likes
QVUser1
Partner - Contributor III
Partner - Contributor III

How to to Create Variable for the Same If Workings Hours is From 9..00 and Evening at 17.30

0 Likes
Version history
Last update:
‎2022-09-21 01:04 PM
Updated by: