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