5 Replies Latest reply on Mar 6, 2017 11:04 PM by Shinichiro Murakami

    Calculating True Average Handle Time (Working Time)

    Kerry Page

      I need to calculate average handle time (AHT), or simply, working minutes that I can average from the time a case was opened until it closed, excluding the time the business is closed (inc. holidays).  I have found many helpful postings and workbooks with lots of formulas and calculations, but none of them get me exactly what I need and I am too unknowledgable with complex formulas to figure it out myself as I really don't completely understand the formulas I've been using.   

       

      I am attaching a workbook that contains time opened and time closed.

       

      What I'm having trouble with is the fact that there are Saturday hours...all of the calcs I'm finding round Saturday and Sundays to Monday.  And holidays?  Is using a parameter string the best method to exclude or is there a better way?  The articles I've found referencing holidays through a parameter string is several years old.

       

      This is what I need specifically:

      Total working minutes between case open and case closed so I can turn it into AHT.

      Business hours are:

       

      Monday - Friday 7 am - 9 pm

      Saturdays 10 am - 3 pm

      Holidays: 05/30/2016,07/04/2016,09/05/2016,11/24/2016,12/25/2016, 01/02/2016

       

      Please include detailed instructions if you have them.  I appreciate it.

        • 1. Re: Calculating True Average Handle Time (Working Time)
          Shinichiro Murakami

          Hi Kerry,

           

          As you may already be aware, this is quite tough request.

           

          Anyways, here is a solution.

          But I could not validate enough, so please let me know if you find any incorrect calculation results.

           

            [Date Open +1]

          date([Date Opened])+1

           

          [Date Close -1]

          date([Date Closed])-1

           

          [Saturday Count]

          ceiling(max(

          0,

          datetrunc('week',[Date Close -1],"Saturday")+1-datetrunc('week',[Date Open +1]+6,"Saturday")

          )/7)

           

          [Sunday Count]

          ceiling(max(

          0,

          datetrunc('week',[Date Close -1],"Sunday")+1-datetrunc('week',[Date Open +1]+6,"Sunday")

          )/7)

           

           

          [Holiday count 01/02/2016]

          if datename('weekday',date(#01/02/2016#))="Satruday"

          or datename('weekday',date(#01/02/2016#))="Sunday"

          or date(#01/02/2016#)<[Date Open +1]

          or date(#01/02/2016#)>[Date Close -1]

          then 0

          else 1

          end

           

          [Holiday count 05/30/2016]

          if datename('weekday',date(#05/30/2016#))="Satruday"

          or datename('weekday',date(#05/30/2016#))="Sunday"

          or date(#05/30/2016#)<[Date Open +1]

          or date(#05/30/2016#)>[Date Close -1]

          then 0

          else 1

          end

           

           

          *

          *

          *

          *

          *

           

          Same thing for All Holidays

           

           

          [First/Last Date Working Hours]

          (

          if date([Date Opened])=date([Date Closed]) then ([Date Closed]-[Date Opened])

          elseif

          date([Date Opened])>date([Date Closed]) then 0

          else

          (if [Weekday open] = "Saturday" then (date([Date Opened])+15/24) -[Date Opened]

          ELSE date([Date Opened])+21/24-[Date Opened] end)

          +

          (if [Weekday close ] = "Saturday" then [Date Closed] - (date([Date Closed])+10/24)

          ELSE  [Date Closed] - (date([Date Closed])+7/24) end)

          END

          )

          *24

           

          [Full Workdays working hours]

          (

          datediff('day',[Date Opened],[Date Closed])-

          ([Holiday count 01/02/2016]+

          [Holiday count 05/30/2016]+

          [Holiday count 07/04/2016]+

          [Holiday count 09/05/2016]+

          [Holiday count 11/24/2016]+

          [Holiday count 12/25/2016]+

          [Saturday Count]+

          [Sunday Count])

          )

          *14

          +

          [Saturday Count]*5

           

           

          [Total Working Hours]

          [Full Workdays working hours]+[First/Last Date Working Hours]

           

           

           

          Thanks,

          Shin

          1 of 1 people found this helpful
          • 2. Re: Calculating True Average Handle Time (Working Time)
            Kerry Page

            Hi Shin,

             

            Wow!  I am truly impressed.  Thank you very much for taking the time to do this - your instructions were very easy to follow.

             

            I did find a few things that aren't adding up as expected.  These examples were not in my first file so I have included them in this workbook (attached with KP initials).

             

            ID 131 - Create date is a 05/21/16 (Saturday) at 1:26 PM, Closed Monday 05/23/16 at 8:13 AM.  It should be around 2 hours, and it is showing 16.8

             

            ID 132 - Create date is on a Sunday, 07/03/16, and the day after is a holiday, so really it did not start until 7/5.  It closed on 07/08/16. It is showing 65.9 hours, and it should be somewhere around 50 if I'm counting right.

             

            I am looking at the "Total Working Hours" column to get these numbers.

             

            Thank you again and I very much appreciate your information!

             

            -Kerry

            1 of 1 people found this helpful
            • 3. Re: Calculating True Average Handle Time (Working Time)
              Shinichiro Murakami

              Wait a moment...

              I don't assume there is working hours on Sunday and Holidays.

              If that's the case, need to re-build the logic.

              Because it means there is extra hours for other days as well..

               

              Let me know IF there is a case start/end is out of the designated time period. That requires coupld of more logic on top of current.

              Monday - Friday 7 am - 9 pm

              Saturdays 10 am - 3 pm

              Holidays: 05/30/2016,07/04/2016,09/05/2016,11/24/2016,12/25/2016, 01/02/2016

               

               

               

              Anyways I modified on formula.

               

              [Full Workdays working hours]

              if datediff('day',[Date Opened],[Date Closed])<=1

              then 0

              else

              (datediff('day',[Date Open +1],[Date Close -1]+1)-

              ([Holiday count 01/02/2016]+

              [Holiday count 05/30/2016]+

              [Holiday count 07/04/2016]+

              [Holiday count 09/05/2016]+

              [Holiday count 11/24/2016]+

              [Holiday count 12/25/2016]+

              [Saturday Count]+

              [Sunday Count]))

              *14

              +

              [Saturday Count]*5

               

               

              end

               

              Thanks,

              Shin

              2 of 2 people found this helpful
              • 4. Re: Calculating True Average Handle Time (Working Time)
                Kerry Page

                Hi Shin,

                 

                I got pulled into another project and had to put this one on hold and am just getting back to it.  I added your additional calculation and it appears to have corrected.  I have spot checked several instances and everything is correct.

                 

                Thank you again - VERY appreciated!!

                 

                Thanks,

                Kerry

                • 5. Re: Calculating True Average Handle Time (Working Time)
                  Shinichiro Murakami

                  Hi Kerry

                   

                  Thank you for the reply. That's OK.

                  Just FYI, for excluding weekend, I posted as common solution in this link.

                   

                  Excel's Networkdays Alternative (Count days excluding Weekend) [version 2]

                   

                   

                   

                  Thanks,Shin