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!
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?
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.
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.
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.
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.