1 2 Previous Next 16 Replies Latest reply on Nov 8, 2016 8:52 PM by Derek Arsenault

    How to Calculate Working Minutes- Excluding Weekends and Holidays

    Felix Caspari

      Hello,

       

      Any help on how to calculate the working minutes, which is a time difference between two (02) date, taking into account the Week day (Monday-Friday), bank holidays, and working business hours (9:00 - 18:00)

       

      Date 1: X1, format: DD/MM/YYYY hh:mm:ss

      Date 2: X2, format: DD/MM/YYYY hh:mm:ss

      Business Hours: 8:00 - 18:00

      List of Bank Holidays: January 1st, May 1st, May 8th...etc

        • 2. Re: How to Calculate Working Minutes- Excluding Weekends and Holidays
          KK Molugu

          Felix.

           

          Not having a function to easily calculate holidays, just curious how many years worth of data you have. Reason for my question is if there is any manual possibility to eliminate holiday hours in a calculation.

          ..kk

          • 3. Re: How to Calculate Working Minutes- Excluding Weekends and Holidays
            Felix Caspari

            Sankar,

             

            Thank you for the response. I tried both alternatives but didn't have any success. I actually had taken a look at the links you sent prior to "re-posting" the question.

             

            Thanks,

            Felix

            • 4. Re: How to Calculate Working Minutes- Excluding Weekends and Holidays
              Felix Caspari

              Karunaker,

               

              Thank you for the response. For this particular analysis I don't have much data (approx 5 years). Of course going forward years will be added to this analysis. I think what the challenge might be is that the application has to understand when the Holiday "occurs" so the "business hours" for that day aren't counted against our response time.

               

              Thanks,

              Felix

              • 5. Re: How to Calculate Working Minutes- Excluding Weekends and Holidays
                KK Molugu

                Felix:

                 

                This was an interesting problem. First to calculate the weekdays and weekends and the killer was the holidays. As I don't think there is any easy function to calculate holidays between two days, I have come up with a manual workaround. This will solve the problem, but when you have more than handful holidays, you have to be careful in writing that formula.

                 

                As I see that you want to calculate minutes between 8 AM and 6 PM, I was thinking that you want to eliminate any minutes before 8 AM and after 6 PM. My total working minutes between days;

                 

                // Start with total minutes and

                // take away non working hour minutes (before 8 AM and after 6 PM)

                // take away night minutes 6 PM to 8 AM

                // take away weekend minutes

                // Take away holiday minutes

                [Total Minutes] -

                [Started Before 8AM] -

                [Ended After 6PM] -

                [6PM To 8AM Minutes] -

                [Weekend Minutes] -

                [Holiday Minutes]

                Capture.PNG

                I have created bunch of calculate fields to keep it clean.

                 

                About the holidays; I have created a parameter with mm/dd/yyyy string separated by COMMA. Using that this is how I calculated number of holidays between start and end dates; (again not so clean but until we have a function that does it its good);

                 

                //Holiday string Parameter

                02/14/2014,03/23/2014,03/27/2014

                 

                // Number of holidays between two days

                if (LEN([Holidays String]) >= 10) then

                    if date(MID([Holidays String], 1,10)) >= [Start Date] AND

                       date(MID([Holidays String], 1,10)) <= [End Date] AND

                       datepart('weekday', date(MID([Holidays String], 1,10))) <> 1 AND

                       datepart('weekday', date(MID([Holidays String], 1,10))) <> 7

                          then 1 else 0 end

                end

                 

                +

                 

                // holiday - #2

                if (LEN([Holidays String]) >= 21) then

                    if date(MID([Holidays String], 12,10)) >= [Start Date] AND

                       date(MID([Holidays String], 12,10)) <= [End Date] AND

                       datepart('weekday', date(MID([Holidays String], 12,10))) <> 1 AND

                       datepart('weekday', date(MID([Holidays String], 12,10))) <> 7

                          then 1 else 0 end

                end

                 

                // +

                // And so on...

                 

                If we have more holidays, this needs to be repeated by changing some numbers

                 

                Please do test with more data, as I used few sample records

                 

                Attached is the workbook and hope this helps;

                 

                ..kk

                • 7. Re: How to Calculate Working Minutes- Excluding Weekends and Holidays
                  manish mishra

                  Plz refer the attached workbook and related excel sheet(as data source).

                  In that working hour of an employee is 9AM-6pm,

                  sat-sun are taken as weekends

                  There is a list of holidays(although I've given this only for year 2012,You can add more dates of other years)

                  All calculated field have given comments for better understanding(how it works).

                   

                  so,tasks done here is-

                  custom sql required to relate both sheets.

                  calculation of all weekends

                  holiday calculation in given date range

                  exclude sat/sun from holidays becz all weekends taken out already

                  calculation of missing hours

                  Hr. difference calculation corresponding to each start time and end time  as office is b/w 9-6 (total hrs-missing hrs)

                  Utilization calculation

                  _______________________________________

                   

                   

                  calculation of missing hours

                       start date                          end date                

                  03/07/2014 3:00pm     04/07/2014 10:00am      

                   

                  here missing hrs means,employee was expected to start work at 9 am while he started at at 3pm,so he missed 6hrs(ie.15-9)

                  again,at end date employee was expected to start work at 6pm while he ended at 10am so again he missed 8hrs(ie.18-10).

                  so,total missing is 6+8=14hrs.


                  although if both start n end time is not in range of 9-6 timings it'll be taken 0,because there is no missing.

                  For details see cal. field in  TWBX

                   

                   

                  calculation of utilization for employee

                   

                  start date                     end date                     hrs      days

                  03/07/2014 3:00pm    03/07/2014 6:00pm      3           1     

                  03/07/2014 3:00pm    04/07/2014 10:00am     4           2     

                   

                   

                  for 1st employee-

                  worked 3 hrs out of 1day=9hrs

                  means,

                  utilization=(total hrs worked)/(total hrs expected) * 100

                  3*100/9 = 33.33%

                   

                  for 2nd employee-

                  worked 4 hrs out of 2day ie (9*2=18 hrs)

                  means,

                  utilization=(total hrs worked)/(total hrs expected) * 100

                  is 4*100/18 = 22.22%.

                   

                  If found any flaws,please make me aware of that.

                  Thanks

                   

                   

                   

                  plz refer this also(just for understanding solution approach in different way)

                  http://community.tableau.com/message/324381#324381

                  • 8. Re: How to Calculate Working Minutes- Excluding Weekends and Holidays
                    manish mishra

                    Hi,well done

                    Your effort is appreciable(because i was also vexed up withh similar problem last week,although i got solution now,will share this week).

                    But few things which I found as a limitation/flaws in this solution.(may be i am wrong,please clarify)

                    1.Do you find it feasible to take list of holidays as a string parameter.

                       What if I have a long list of holidays of 4-5 years in an another sheet.

                     

                    2.How to related 2 sheets(ie. 'holiday list' and 'employee work tenure')..Is that blending/joins/some other way

                       How it works if a new employee enter in the data-set with a date range that is not found around holiday-dates.(How to make it dyanamic?)

                     

                    3.A caution should be taken while calculating actual holiday because there may be few holidays which falls on sat-     sun,so in that way, countable holidays=[all holidays in date range] - [Exclude holidays which falls on weekends]

                    because all weekends taken out already.

                     

                    4.What if employee of certain region(if region is also a column) follows weekends as fri and sat(instead of sat-sun),

                       does the same calculated field to exclude weekends will work here.(although i think -case/when/then will be a better approach here sth like --when region is "gulf " weekend=......when "rest others" then weekend=....(formula what u wrote).....)

                     

                    Thanks,

                    manish

                    • 9. Re: How to Calculate Working Minutes- Excluding Weekends and Holidays
                      Łukasz Majewski

                      Some time ago I needed such a calculation so I created a SQL function attached (WorkTime4).

                      It is tsql (MS SQL Server) but can be translated to another target, I guess. It takes a bunch of parameters:

                      meaning:

                      • @t1                        your start datetime
                      • @t2                        your end datetime
                      • @weekstart            time work starts on weekdays
                      • @weekend             time work ends on weekdays
                      • @Satstart               time work starts on Saturday
                      • @Satend                time work ends on Saturday
                      • @Sunstart              time work starts on Sunday
                      • @Sunend               time work ends on Sunday
                      • @Saturday             first day of weekend (defaults to 6 <Saturday>)
                      • @Sunday               second day of weekend (defaults to 7)

                       

                      Weekend start & end times default to 00:00 meaning no business on those days. If you put NULL into any start/end parameters it will mean 24 hour workday.

                       

                      The result is seconds (integer). When aggregating long time spans casting into bigint is recommended to avoid overflow.

                       

                      The function does not take additional holidays parameters but it may be worked around. You may join your [dates] table with [holidays] table so that you split the date range with holidays, calculate these new individual time spans and sum them up.

                       

                      I created a random set of dates & holidays and joined them like this:

                       

                       

                      select x.r, x.date1, x.date2, x.[start on], x.[end on], x.[Working Seconds], x.[Working Minutes], x.[Working Days],

                      SUM(case when x.rr=1 then dbo.WorkTime4(x.date1,case when x.rrr=1 then x.date2 else  x.holiday end,'08:00','18:00',default, default, default, default, x.Saturday, x.Sunday) else 0 end

                          +case when x.holiday is null then 0 else dbo.WorkTime4(case when x.holiday=x.date2 then x.date2 else dateadd(day,1,x.holiday) end ,case when x.rrr=1 then x.date2 else  y.holiday end,'08:00','18:00',default, default, default, default, x.Saturday, x.Sunday) end) [WorkSec excl Holidays],

                      SUM(case when x.rr=1 then dbo.WorkTime4(x.date1,case when x.rrr=1 then x.date2 else  x.holiday end,'08:00','18:00',default, default, default, default, x.Saturday, x.Sunday) else 0 end

                          +case when x.holiday is null then 0 else dbo.WorkTime4(case when x.holiday=x.date2 then x.date2 else dateadd(day,1,x.holiday) end ,case when x.rrr=1 then x.date2 else  y.holiday end,'08:00','18:00',default, default, default, default, x.Saturday, x.Sunday) end)/60. [WorkMin excl Holidays],

                      SUM(case when x.rr=1 then dbo.WorkTime4(x.date1,case when x.rrr=1 then x.date2 else  x.holiday end,'08:00','18:00',default, default, default, default, x.Saturday, x.Sunday) else 0 end

                          +case when x.holiday is null then 0 else dbo.WorkTime4(case when x.holiday=x.date2 then x.date2 else dateadd(day,1,x.holiday) end ,case when x.rrr=1 then x.date2 else  y.holiday end,'08:00','18:00',default, default, default, default, x.Saturday, x.Sunday) end)/10.0/3600 [WorkDay excl Holidays]

                       

                      from

                      (

                      select r,date1, date2, h.holiday, DATENAME(weekday,date1) [start on], DATENAME(weekday,date2) [end on], Saturday, Sunday,

                              dbo.WorkTime4(date1, date2,'08:00','18:00',default, default, default, default, Saturday, Sunday) [Working Seconds],

                              dbo.WorkTime4(date1, date2,'08:00','18:00',default, default, default, default, Saturday, Sunday)/60.0 [Working Minutes],

                              dbo.WorkTime4(date1, date2,'08:00','18:00',default, default, default, default, Saturday, Sunday)/10/3600.0 [Working Days]

                      ,        ROW_NUMBER() over (partition by d.r order by h.holiday asc) rr, ROW_NUMBER() over (partition by d.r order by h.holiday desc) rrr

                      from (select *, ROW_NUMBER() over (order by (select 1)) r from dates$ )d

                      left join holidays$ h ON h.holiday between d.date1 and d.date2

                      ) x

                      left join

                      (

                      select r,date1, date2, h.holiday, DATENAME(weekday,date1) [start on], DATENAME(weekday,date2) [end on], Saturday, Sunday,

                              dbo.WorkTime4(date1, date2,'08:00','18:00',default, default, default, default, Saturday, Sunday) [Working Seconds],

                              dbo.WorkTime4(date1, date2,'08:00','18:00',default, default, default, default, Saturday, Sunday)/60.0 [Working Minutes],

                              dbo.WorkTime4(date1, date2,'08:00','18:00',default, default, default, default, Saturday, Sunday)/10/3600.0 [Working Days]

                      ,        ROW_NUMBER() over (partition by d.r order by h.holiday asc) rr

                      from (select *, ROW_NUMBER() over (order by (select 1)) r from dates$ )d

                      left join holidays$ h ON h.holiday between d.date1 and d.date2

                      ) y

                      ON x.r=y.r and x.rr=y.rr-1

                      GROUP BY x.r, x.date1, x.date2, x.[start on], x.[end on], x.[Working Seconds], x.[Working Minutes], x.[Working Days]

                       

                       

                      Note I used date ranges all starting/ending at midnight.

                      Oh and I used '08:00' as start time rather than '09:00' therefore division by 10 in "days" column...

                      • 10. Re: How to Calculate Working Minutes- Excluding Weekends and Holidays
                        Łukasz Majewski

                        I had a second thought on this and want to share a much simpler SQL yielding exactly the same results:

                         

                        select d.date1, d.date2, d.Saturday, d.Sunday

                        , max(dbo.WorkTime4(date1, date2,'08:00','18:00',default, default, default, default, Saturday, Sunday)) [Work Time]

                        , sum(dbo.WorkTime4(

                                case when h.holiday=cast(d.date1 as date) then d.date1 else h.holiday end,

                                case when h.holiday=cast(d.date2 as date) then d.date2 else dateadd(day,1,h.holiday) end,

                                '08:00','18:00', default, default, default, default, Saturday, Sunday)) [Holiday Time]

                         

                        , max(dbo.WorkTime4(date1, date2,'08:00','18:00',default, default, default, default, Saturday, Sunday))

                          -

                          sum(case when h.holiday is not null then dbo.WorkTime4(

                                case when h.holiday=cast(d.date1 as date) then d.date1 else h.holiday end,

                                case when h.holiday=cast(d.date2 as date) then d.date2 else dateadd(day,1,h.holiday) end,

                                '08:00','18:00', default, default, default, default, Saturday, Sunday) else 0 end) [The Work Time]

                         

                        from dates$ d left join holidays$ h ON h.holiday between cast(d.date1 as date) and cast(d.date2 as date)

                        group by d.date1, d.date2, d.Saturday, d.Sunday

                         

                         

                        So this time the idea is to subtract holiday time from overall work time using the same function.

                         

                        _______________________________________________________________________________________

                         

                        I have also made this calculation in Tableau.although I recommend doing this in data source as above.

                        So the case is this: calculate work time in seconds taking into account work hours, weekends and holidays from:

                        • table of datetime ranges ([date1] - [date2])
                        • table of holidays ([holiday])

                        These are two separate tables with arbitrary number of records.

                         

                        In order to achieve this one must connect Tableau workbook to the two tables as LEFT OUTER JOIN (preferred) or CROSS JOIN (last resort). I do not believe this can be achieved by "data blending" in tableau.

                         

                        In the attached sample workbook I am using Excel sheets as source joined like this:

                         

                         

                        The result looks like this:

                         

                        I used the same data as in my previous post so your weekend days are specified along with datetime ranges. To allow work hours specification I used these parameters:

                        They are integers where two left digits stand for hours (HH) and the other two minutes (mm). Hence 1800 is '18:00', 800 is '08:00' - you get the idea. Unfortunately Tableau lacks 'time' datatype...

                         

                        I have also included sheets with cross join solutions but the one in 'Holidays (left join)' is the simplest and most effective.

                         

                         

                        EDIT: attached a 9.0 twbx with corrected calculations - as spotted here by himhim chan - previously it was correct if full hours were used in start/end times parameters; now it should be fine.

                        • 11. Re: How to Calculate Working Minutes- Excluding Weekends and Holidays
                          kettan

                          If your source is Excel, then you could calculate holidays there as shown in  Re: How to caliculate public holidays ?

                          • 12. Re: How to Calculate Working Minutes- Excluding Weekends and Holidays
                            Mary Solbrig

                            This is actually not my solution but a friend of mine, but it seemed like it should be posted in the forums somewhere:

                             

                            Step 0:  Create two Parameters for the Start and End of Day Hours (Integer values, Step size: 1, Range: 1-11)

                            + Day Start Hour (am)

                            + Day End Hour (pm)

                             

                            Step 1: Evaluate Day of week Ticket was Submitted

                             

                            [!Round to Monday]=

                             

                            IF DATENAME('weekday',[Ticket Submitted]) = "Saturday"
                            THEN DATEADD('day',2,[Ticket Submitted])
                            ELSEIF DATENAME('weekday',[Ticket Submitted]) = "Sunday"
                            THEN DATEADD('day',1,[Ticket Submitted])
                            ELSE [Ticket Submitted]
                            END

                             

                            Step 2:  If it was moved to Monday, move it to the beginning of the day on Monday.

                             

                            [!Round to Open]=

                             

                            IF [!Round to Monday] > [Ticket Submitted]
                            THEN DATEADD('minute',INT([Day Start Hour (am)]*60),DATETRUNC('day',[!Round to Monday]))
                            ELSEIF [!Round to Monday] <
                            DATEPARSE("MM/dd/yyy HH:mm", STR(MONTH([!Round to Monday]))+"/"+STR(DAY([!Round to Monday]))+"/"+STR(YEAR([!Round to Monday]))+" "+STR([Day Start Hour (am)])+":00")
                            THEN DATEADD('minute',INT([Day Start Hour (am)]*60),DATETRUNC('day',[!Round to Monday]))
                            ELSE
                            [Ticket Submitted]
                            END

                             

                            Step 3: Find difference in minutes between ticket submission time and end of day.

                            IF minutes between !Round to Open and close of day is less than zero, THEN: 0 (ticket submitted after EOD)

                            ELSE: The difference in minutes between the ticket submission time and the end of day

                             

                            [!SubmitTimeFirstDay]=

                             

                            IF
                            DATEDIFF('minute',
                            [!Round to Open],
                            DATEPARSE("MM/dd/yyy HH:mm", STR(MONTH([!Round to Open]))+"/"+STR(DAY([!Round to Open]))+"/"+STR(YEAR([!Round to Open]))+" "+STR([Day End Hour (pm)]+12)+":00"))
                            < 0 THEN 0
                            ELSE
                            DATEDIFF('minute',
                            [!Round to Open],
                            DATEPARSE("MM/dd/yyy HH:mm", STR(MONTH([!Round to Open]))+"/"+STR(DAY([!Round to Open]))+"/"+STR(YEAR([!Round to Open]))+" "+STR([Day End Hour (pm)]+12)+":00"))
                            END

                             

                            Step 4: Take difference in minutes between start of day and First Response.

                             

                            [!ResponseTimeLastDay]=

                             

                            DATEDIFF('minute',
                            DATEPARSE("MM/dd/yyy HH:mm", STR(MONTH([First Response]))+"/"+STR(DAY([First Response]))+"/"+STR(YEAR([First Response]))+" "+STR([Day Start Hour (am)])+":00"),
                            [First Response])

                             

                            Step 5: Multiply the number of business days in between adjusted time and first response by the number of hours worked in a day.

                             

                            [!Minutes_in_Between] =

                             

                            (DATEDIFF('week',[!Round to Open],[First Response])*5
                            +
                            MIN(DATEPART('weekday',[First Response]),6)
                            -
                            MIN(DATEPART('weekday',[!Round to Open]),6)
                            -1)
                            *
                            INT((12-[Day Start Hour (am)])+[Day End Hour (pm)])*60

                             

                            Step 6:  Add values from Steps 3-5.

                            Sum absolute values, to not penalize for 'negative' minutes resulting from responding before start of day.

                            Keep Possible negative values for the !Minutes_in_Between, to correctly calculate tickets that were responded to on the same day they were submitted.

                             

                            [Total Time] =

                            ABS([!SubmitTimeFirstDay]) + ([!Minutes_in_Between]) + ABS([!ResponseTimeLastDay])

                            1 of 1 people found this helpful
                            • 13. Re: How to Calculate Working Minutes- Excluding Weekends and Holidays
                              Łukasz Majewski

                              Two notes on Mary's friend's solution:

                               

                              1. It seems to be an algorithm specialized for those tickets response time since in case when response happens after [End of Day Hour] it still counts the minutes between [end of day] and [response time] - see ticket name "I"

                               

                              2. Due to string/date conversion & parsing, in case of large data set, the calculation takes longer than if it was integer based only:

                              Apart from that it looks to be right.

                               

                               

                              Attached are your and my solutions with the same data - enlarged with random dates to about 200000 rows.

                              • 14. Re: How to Calculate Working Minutes- Excluding Weekends and Holidays
                                vamsi potti

                                Hi,

                                The calculation which is provided by you is working fine, however I am trying to add an holiday which is on 3rd of April 2015, I have used the same formula (Extended accordingly), however few tickets were raised on 3rd of April which should not be a part of calculation, I mean the formula should exclude the holidays and it is not happening, kindly help me on the formula what need to be update in "holiday string" and "holiday" calculated field.

                                 

                                Regards,

                                Vamsi.

                                1 2 Previous Next