7 Replies Latest reply: May 27, 2011 1:45 AM by 691371 RSS

    Time Passage Calculation excluding Weekends and Non-work hours

    717716
      Good Afternoon Everyone!

      I am currently using the expression below to determine the amount of time that has passed since a Task has been created.

      TIMESTAMPDIFF(SQL_TSI_MINUTE, Activity."Created Date", NOW())

      We have a completion goal for a specific task type of 2 hours (120 minutes), but it should only be calculated minutes between 7 AM and 4 PM and only on week days.

      So, a task created at 3:50 PM on a Friday that is completed at 7:30 AM on the following Monday should only calculate to 40 minutes of time.

      I am at a loss for where to start tackling this problem. If any of you can help I would be very appreciative!
        • 1. Re: Time Passage Calculation excluding Weekends and Non-work hours
          796572
          I am looking for the same requirement... Query all SRs that are between 2.5 to 4 hours from creation time, between working hours 8-5, excluding weekends.

          Udaya
          • 2. Re: Time Passage Calculation excluding Weekends and Non-work hours
            763243
            Hey Greg,

            No issues with the calculation, I will help you out on this.

            Just wanted to confirm that does the weekdays calculation include public holidays also. I mean when we calculate the SLA, do we need to calculate if the weekday is a holiday or not?

            Regards,

            Paul
            • 3. Re: Time Passage Calculation excluding Weekends and Non-work hours
              758799
              Hi Paul, can you post your solutions, I have the same requirement to calculate the working time, only Monday to Friday, 8:00am to 5:00pm are calcuated. thanks!!
              • 4. Re: Time Passage Calculation excluding Weekends and Non-work hours
                763243
                Hello Team,

                The best solution would be to have a custom component to calculate the SLA and populate the value in the field. Pull up the value in the field.

                Let me know if this is acceptable or you still want to populate using reports.

                Regards

                Paul
                • 5. Re: Time Passage Calculation excluding Weekends and Non-work hours
                  691371
                  I will make a couple of assumptions for building the expression here:

                  Imagine your working hours in a day are fixed i.e. 7 AM to 4 PM(if this gets changed, you will have to modify the formula accordingly unless we can think of something to automate it) and you will have fields like Activity Start time and Activity End time. Also you wont have any activity started on a weekend or a public holiday.
                  then at max there can be 2 scenarios:
                  1) Activity that is completed on the same day
                  2) activity that is completed on some other day(like next day or day after)

                  so my Fx would be something like this:-

                  CASE WHEN DAY(Activity Start Time) = DAY(Activity End Time) THEN TIMESTAMPDIFF(SQL_TSI_MIN,activity start time,activity end time) ELSE TIMESTAMPDIFF(SQL_TSI_MIN,activity start time,activity end time) - (60*15*(DAY(Activity End Time) - DAY(Activity Start Time))) END

                  The above formula will take care of weekends and public holidays. 15 here represents the hours from 4 PM to 7 AM on next day and since we are calculating minutes, its multiplied by 60.
                  • 6. Re: Time Passage Calculation excluding Weekends and Non-work hours
                    763243
                    Hello Max,

                    How does the formula ELSE TIMESTAMPDIFF(SQL_TSI_MIN,activity start time,activity end time) - (60*15*(DAY(Activity End Time) - DAY(Activity Start Time))) END
                    take care of the holidays and weekends?

                    How would the function TIMESTAMPDIFF identify the holidays and weekends.
                    • 7. Re: Time Passage Calculation excluding Weekends and Non-work hours
                      691371
                      Hey Paul, thanks for the prompt. I see now that the formula is incomplete.
                      As I have just taken the difference in days between the Activity Start Time and End Time, it will consider any weekend. But its incomplete as it considers only 15 hours of a day and not the remaining 9 hours for weekends. For holidays - we do need a field or something to identify it like Activity Type = 'holiday'.

                      So the ELSE* part of revised formula would be something like this:

                      ... ELSE TIMESTAMPDIFF(SQL_TSI_MIN,activity start time,activity end time) - (60*15*(DAY(Activity End Time) - DAY(Activity Start Time))) - (60*9*(COUNT(DISTINCT CASE WHEN DAYOFWEEEK(Date.Date) IN (6,7) OR ~some field to identify holiday~ = 'something' THEN 1 END)) END

                      now imagine there's one activity that started on Friday 7:00 AM and ends on coming Monday at 4:00 PM.
                      Actual duration is 18 hours.

                      Now,the first term after ELSE will give 81*60 minutes. next term should give 45*60 minutes. third term would be 18*60 min.
                      so the entire expression would return 18 hours.

                      Even if there's a holiday, then i guess the 'OR' part should capture it.

                      I hope this makes sense. Please let me know if it should need any further corrections.