1 2 Previous Next 16 Replies Latest reply on Apr 4, 2016 1:52 AM by mahi reddy

    Lifetime of a ticket over business hours 6am-6pm

    lucas grim

      I am trying to calculate the hours of a ticket's lifetime over business hours based on a start date/time and an end date/time. I have looked over a few threads that exclude holidays and weekends from calculations but some of them seem to use a method that would not work where they shifted the start date rather than excluding weekends.

       

      For example if a ticket had a start date/time of 10/23/2015 4:00 PM (Friday at 4) and an end date/time of 10/26/2015 9:00 AM (Monday at 9 am) then it would have a business time of completion to be 5 hours as opposed to 65 hours that results from Datediff("hour",[Submit Date],[Last Resolved Date])

       

       

       

      Any help would be appreciated. I have tried a few forum post's approaches to no avail. I have attached the relevant parts of a sample of tickets.

       

      Thank You!

        • 1. Re: Lifetime of a ticket over business hours 6am-6pm
          John Sobczak

          Something along these lines should get what's needed:

           

           

          DATEDIFF('hour', [Submit Date], [Last Resolved Date])

           

          -

           

          (DATEDIFF('day',[Submit Date],[Last Resolved Date]) * 16)

           

          -

           

          ( DATEDIFF('week',[Submit Date],[Last Resolved Date],'monday') * 16)

          • 2. Re: Lifetime of a ticket over business hours 6am-6pm
            lucas grim

            I tried that out on the excel i posted as well as the example i wrote about

             

            "if a ticket had a start date/time of 10/23/2015 4:00 PM (Friday at 4) and an end date/time of 10/26/2015 9:00 AM (Monday at 9 am) then it would have a business time of completion to be 5 hours as opposed to 65 hours"

             

            when i plugged that example in i came out with a result of 1. (65)-(48)-(16) = 1

             

            I need more than just a calculation of hours worked per day. The only hours that count would be the ones within the time period of 6am-6pm and exclude any time outside of M-F 6am-6pm that the ticket is "alive".

            • 3. Re: Lifetime of a ticket over business hours 6am-6pm
              John Sobczak

              How about below with maybe some additional tweaking?  You will have to figure out the calc to get the Submit Date and Last Resolved Date rounded to 6am and 6pm but I'm sure it's possible.

               

              if DATEDIFF('day', [Submit Date],[Last Resolved Date]) = 0 then DATEDIFF('hour',  [Submit Date], [Last Resolved Date])

              else

              DATEDIFF('hour', [Submit Date], [Submit Date]@6PM)    //Hours on First Day

               

              + DATEDIFF('hour', [Last Resolved Date]@6AM, [Last Resolved Date])   //Hours on Final Day

               

              +(DATEDIFF(‘day’, [Submit Date], [Last Resolved Date])-1) * 8       //Hours for days between

               

              -(DATEDIFF('week', [Submit Date],[Last Resolved Date],'monday') * 16)     // Minus Hours for Weekends

               

              end

              • 4. Re: Lifetime of a ticket over business hours 6am-6pm
                John Sobczak

                I modified it further:

                 

                 

                if DATEDIFF('day', [Submit Date],[Last Resolved Date]) = 0 then DATEDIFF('hour',  [Submit Date], [Last Resolved Date])

                else

                DATEDIFF('hour', [Submit Date], [Submit Date]@6PM)    //Hours on First Day

                 

                + DATEDIFF('hour', [Last Resolved Date]@6AM, [Last Resolved Date])   //Hours on Final Day

                 

                +(DATEDIFF(‘day’, [Submit Date], [Last Resolved Date])-1) * 12      //Hours for days between

                 

                -(DATEDIFF('week', [Submit Date],[Last Resolved Date],'monday') * 24)     // Minus Hours for Weekends

                end

                1 of 1 people found this helpful
                • 5. Re: Lifetime of a ticket over business hours 6am-6pm
                  lucas grim

                  Hmm, that concept looks really solid, now i just have to figure out how to implement it within the data set. Thanks!

                   

                  im thinking creating another field that calculates the [submit date]@6pm by using DATETRUNC('day',[submit date]) then setting the time for that variable to 6pm

                  • 6. Re: Lifetime of a ticket over business hours 6am-6pm
                    John Sobczak

                    Yes the DATETRUNC should do it along with DATEADD ('hour', 6 or 18, [Submit Date or Last Resolved Date truncated to day]).

                    1 of 1 people found this helpful
                    • 7. Re: Lifetime of a ticket over business hours 6am-6pm
                      lucas grim

                      im now using:

                       

                      if

                           DATEDIFF('day', [Submit Date],[Last Resolved Date]) = 0

                      then

                           DATEDIFF('hour',  [Submit Date], [Last Resolved Date])

                      else

                           DATEDIFF('hour', [Submit Date], [submit day])

                           + DATEDIFF('hour', [last date], [Last Resolved Date])

                           +(DATEDIFF('day', [Submit Date], [Last Resolved Date])-1) * 12

                           -(DATEDIFF('week', [Submit Date],[Last Resolved Date],'monday') * 24)

                      end

                       

                       

                       

                      where

                           [submit day] = DATEADD('hour',18,DATETRUNC('day',[Submit Date]))
                      and
                           [last date] = DATEADD('hour',18,DATETRUNC('day',[Last Resolved Date]))

                       

                      but I have noticed that if a ticket is submitted on a sunday and resolved on a monday, it becomes negative. Im trying to change the if else statements to accommodate these possibilities.

                      • 8. Re: Lifetime of a ticket over business hours 6am-6pm
                        John Sobczak

                        Maybe creat a modified submit date where if it submitted after 6pm on Friday then equals 6 AM on Monday.  You will need to something similar for Last Resolved Date as well.

                        • 9. Re: Lifetime of a ticket over business hours 6am-6pm
                          kettan

                          Here is a calculation that combines  John Sobczak's  with  Calculating the Number of Business Days Between Two Dates | Tableau Software  plus extra validating logic:

                           

                          // hours start date
                          IF     DATEPART('weekday',[Start Date Time]) = 1 THEN 0
                          ELSEIF DATEPART('weekday',[Start Date Time]) = 7 THEN 0
                          ELSE   DATEDIFF('hour',[Start Date Time 2],DATEADD('hour',18,DATETRUNC('day',[Start Date Time])))
                          END +
                          
                          // hours end date
                          IF     DATEPART('weekday',[End Date Time]) = 1 THEN 0
                          ELSEIF DATEPART('weekday',[End Date Time]) = 7 THEN 0
                          ELSE   DATEDIFF('hour',DATEADD('hour',6,DATETRUNC('day',[End Date Time])),[End Date Time 2])
                          END +
                          
                          // the other days
                          12 * (
                            DATEDIFF('week',[Start Date Time],[End Date Time])*5
                          + MIN(DATEPART('weekday',[End Date Time]),6)
                          - MIN(DATEPART('weekday',[Start Date Time]),6)
                          - IF     DATEPART('weekday',[End Date Time]) = 1 THEN 0
                            ELSEIF DATEPART('weekday',[End Date Time]) = 7 THEN 0
                            ELSE 1
                            END
                          ) +
                          
                          // holiday 28 October 2015
                          IF #2015-10-28# > [Start Date Time] AND #2015-10-28# < DATETRUNC('day',[End Date Time]) THEN -1
                          ELSE 0
                          END
                          

                           

                          Holidays are added since  Shawn Wallwork  mentioned this issue. These are a pain and therefore added a single manual example to illustrate one way to do it. This would be much easier if something like  Custom Calendar Tables  was implemented and therefore hope mentioned idea gets many more up-votes.

                           

                          If you use this one, I recommend you to test all thinkable scenarios. I so did, but just thought it also needs to be tested for multiple weekends with start and end dates touching weekends. I can't do so now myself, because I need to do something else.

                           

                          thread 192511 Lifetime of a ticket over business hours 6am-6pm.png

                           

                          Attached Workbook Version:  9.0

                          .

                          1 of 1 people found this helpful
                          • 10. Re: Lifetime of a ticket over business hours 6am-6pm
                            Shawn Wallwork

                            But what about the holidays?

                            • 11. Re: Lifetime of a ticket over business hours 6am-6pm
                              kettan

                              But what about the holidays?

                              Good point. I just hard-coded an imagined holiday (28 October 2015) into it and updated  this comment.

                              That said, it would be so much nicer if we had something like  Custom Calendar Tables 

                              1 of 1 people found this helpful
                              • 12. Re: Lifetime of a ticket over business hours 6am-6pm
                                kettan

                                I have noticed that if a ticket is submitted on a sunday and resolved on a monday, it becomes negative.

                                Oops, my calculation was logically built to always included the time on start and end dates.

                                This is corrected and hope the calculation returns correct hours now. See details in  this comment.

                                • 13. Re: Lifetime of a ticket over business hours 6am-6pm
                                  lucas grim

                                  Sory to return to this after so long, but I have run into an issue that maybe you can help me with. I am trying to use the same calculation to filter out holidays, but rather than coding in the actual date, I would like to use a list in excel. Here is the code im using:

                                  // hours start date

                                  IF DATEPART('day',[Start Date])= DATEPART('day',[Holiday]) then 0

                                  ELSE

                                  IF     DATEPART('weekday',[Start Date]) = 1 THEN 0

                                  ELSEIF DATEPART('weekday',[Start Date]) = 7 THEN 0

                                  ELSE   DATEDIFF('hour',[Start Date],DATEADD('hour',18,DATETRUNC('day',[Start Date])))

                                  END

                                  END +

                                   

                                  // hours end date

                                  IF DATEPART('day',[End Date])= DATEPART('day',[Holiday]) then 0

                                  ELSE

                                  IF     DATEPART('weekday',[End Date]) = 1 THEN 0

                                  ELSEIF DATEPART('weekday',[End Date]) = 7 THEN 0

                                  ELSE   DATEDIFF('hour',DATEADD('hour',6,DATETRUNC('day',[End Date])),[End Date])

                                  END

                                  END +

                                   

                                  // the other days

                                  12 * (

                                    DATEDIFF('week',[Start Date],[End Date])*5

                                  + MIN(DATEPART('weekday',[End Date]),6)

                                  - MIN(DATEPART('weekday',[Start Date]),6)

                                  - IF     DATEPART('weekday',[End Date]) = 1 THEN 0

                                    ELSEIF DATEPART('weekday',[End Date]) = 7 THEN 0

                                    ELSE 1

                                    END -

                                   

                                  //Holidays

                                    IF [Holiday] > (DATEPART('day', [End Date])) AND [Holiday] < (DATEPART('day',[Start Date])) then 1

                                    ELSE 0

                                    END

                                  )

                                   

                                  I have included the list intend to use(i have it in an excel file but i cannot figure out how to attach it) but when I try to run it i get errors about aggregation and data sources and other things. If you are testing with the superstore i believe that order date and shipped date can be used in leu of start and end date.

                                   

                                  I dont account for the possibility that the holidays fall on the weekends because the list i am using is government holidays and therefore would never fall on a weekend. -in order for results in the superstore, you may have to switch all the 2015 to 2014.

                                   

                                     

                                  daynameholiday
                                  Thursday, January 1New Year’s Day1/1/2015
                                  Monday, January 19Birthday of Martin Luther King, Jr.1/19/2015
                                  Monday, February 16*Washington’s Birthday2/16/2015
                                  Monday, May 25Memorial Day5/25/2015
                                  Friday, July 3**Independence Day6/3/2015
                                  Monday, September 7Labor Day9/7/2015
                                  Monday, October 12Columbus Day10/12/2015
                                  Wednesday, November 11Veterans Day11/11/2015
                                  Thursday, November 26Thanksgiving Day11/26/2015
                                  Friday, December 25Christmas Day12/26/2015

                                   

                                   

                                  Thank You.

                                  • 14. Re: Lifetime of a ticket over business hours 6am-6pm
                                    mahi reddy

                                    Thanks kettan

                                     

                                    I am also facing the same issue. The solution which you have mentioned resolved my issue.

                                     

                                    I did a slight modification to the calculated field which you have created:

                                     

                                    I have removed the 'Datetrunc' function, because while using DATETRUNC('day',[End Date Time]) function also it is still considering hours instead of days.

                                     

                                    Modified calculated field:

                                     

                                    // holiday 28 October 2015

                                    IF #2015-10-28# > [Start Date Time] AND #2015-10-28# < [End Date Time] THEN -12

                                    ELSE 0

                                    END

                                     

                                    Q) I have the holiday list like January 1, January 19...and so on. I have to apply the above calculation for every date in the holiday list? or any alternate solution?

                                    1 2 Previous Next