This discussion is archived
7 Replies Latest reply: May 26, 2011 11:45 PM by 691371 RSS

Time Passage Calculation excluding Weekends and Non-work hours

717716 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points