Business Working Hours Calculation

    Hi Qlikers,

     

    Sometimes we can have requirement to calculate the business working hours between two Dates due to certain reasons. There are many solutions available on community but I thought let's create the document so that everyone can utilize this.

     

    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

     

    Please see the attached QVW.

     

    Feel free to provide any suggestions.

     

    Regards,

    Kushal