
1. Re: How to Calculate Working Minutes Excluding Weekends and Holidays
Sankarmagesh Rajan Oct 28, 2014 2:56 AM (in response to Felix Caspari) 
2. Re: How to Calculate Working Minutes Excluding Weekends and Holidays
KK Molugu Oct 28, 2014 4:01 AM (in response to Felix Caspari)Felix.
Not having a function to easily calculate holidays, just curious how many years worth of data you have. Reason for my question is if there is any manual possibility to eliminate holiday hours in a calculation.
..kk

3. Re: How to Calculate Working Minutes Excluding Weekends and Holidays
Felix Caspari Oct 28, 2014 8:09 AM (in response to Sankarmagesh Rajan)Sankar,
Thank you for the response. I tried both alternatives but didn't have any success. I actually had taken a look at the links you sent prior to "reposting" the question.
Thanks,
Felix

4. Re: How to Calculate Working Minutes Excluding Weekends and Holidays
Felix Caspari Oct 28, 2014 8:14 AM (in response to KK Molugu)Karunaker,
Thank you for the response. For this particular analysis I don't have much data (approx 5 years). Of course going forward years will be added to this analysis. I think what the challenge might be is that the application has to understand when the Holiday "occurs" so the "business hours" for that day aren't counted against our response time.
Thanks,
Felix

5. Re: How to Calculate Working Minutes Excluding Weekends and Holidays
KK Molugu Oct 30, 2014 11:28 AM (in response to Felix Caspari)Felix:
This was an interesting problem. First to calculate the weekdays and weekends and the killer was the holidays. As I don't think there is any easy function to calculate holidays between two days, I have come up with a manual workaround. This will solve the problem, but when you have more than handful holidays, you have to be careful in writing that formula.
As I see that you want to calculate minutes between 8 AM and 6 PM, I was thinking that you want to eliminate any minutes before 8 AM and after 6 PM. My total working minutes between days;
// Start with total minutes and
// take away non working hour minutes (before 8 AM and after 6 PM)
// take away night minutes 6 PM to 8 AM
// take away weekend minutes
// Take away holiday minutes
[Total Minutes] 
[Started Before 8AM] 
[Ended After 6PM] 
[6PM To 8AM Minutes] 
[Weekend Minutes] 
[Holiday Minutes]
I have created bunch of calculate fields to keep it clean.
About the holidays; I have created a parameter with mm/dd/yyyy string separated by COMMA. Using that this is how I calculated number of holidays between start and end dates; (again not so clean but until we have a function that does it its good);
//Holiday string Parameter
02/14/2014,03/23/2014,03/27/2014
// Number of holidays between two days
if (LEN([Holidays String]) >= 10) then
if date(MID([Holidays String], 1,10)) >= [Start Date] AND
date(MID([Holidays String], 1,10)) <= [End Date] AND
datepart('weekday', date(MID([Holidays String], 1,10))) <> 1 AND
datepart('weekday', date(MID([Holidays String], 1,10))) <> 7
then 1 else 0 end
end
+
// holiday  #2
if (LEN([Holidays String]) >= 21) then
if date(MID([Holidays String], 12,10)) >= [Start Date] AND
date(MID([Holidays String], 12,10)) <= [End Date] AND
datepart('weekday', date(MID([Holidays String], 12,10))) <> 1 AND
datepart('weekday', date(MID([Holidays String], 12,10))) <> 7
then 1 else 0 end
end
// +
// And so on...
If we have more holidays, this needs to be repeated by changing some numbers
Please do test with more data, as I used few sample records
Attached is the workbook and hope this helps;
..kk

6. Re: How to Calculate Working Minutes Excluding Weekends and Holidays
KK Molugu Nov 12, 2014 6:42 AM (in response to Felix Caspari)Did that help Felix...

7. Re: How to Calculate Working Minutes Excluding Weekends and Holidays
Manish Mishra Jan 8, 2015 10:29 PM (in response to Felix Caspari)Plz refer the attached workbook and related excel sheet(as data source).
In that working hour of an employee is 9AM6pm,
satsun are taken as weekends
There is a list of holidays(although I've given this only for year 2012,You can add more dates of other years)
All calculated field have given comments for better understanding(how it works).
so,tasks done here is
custom sql required to relate both sheets.
calculation of all weekends
holiday calculation in given date range
exclude sat/sun from holidays becz all weekends taken out already
calculation of missing hours
Hr. difference calculation corresponding to each start time and end time as office is b/w 96 (total hrsmissing hrs)
Utilization calculation
_______________________________________
calculation of missing hours
start date end date
03/07/2014 3:00pm 04/07/2014 10:00am
here missing hrs means,employee was expected to start work at 9 am while he started at at 3pm,so he missed 6hrs(ie.159)
again,at end date employee was expected to start work at 6pm while he ended at 10am so again he missed 8hrs(ie.1810).
so,total missing is 6+8=14hrs.
although if both start n end time is not in range of 96 timings it'll be taken 0,because there is no missing.
For details see cal. field in TWBX
calculation of utilization for employee
start date end date hrs days
03/07/2014 3:00pm 03/07/2014 6:00pm 3 1
03/07/2014 3:00pm 04/07/2014 10:00am 4 2
for 1st employee
worked 3 hrs out of 1day=9hrs
means,
utilization=(total hrs worked)/(total hrs expected) * 100
3*100/9 = 33.33%
for 2nd employee
worked 4 hrs out of 2day ie (9*2=18 hrs)
means,
utilization=(total hrs worked)/(total hrs expected) * 100
is 4*100/18 = 22.22%.
If found any flaws,please make me aware of that.
Thanks
plz refer this also(just for understanding solution approach in different way)

working hr employee.xlsx 15.7 KB

8. Re: How to Calculate Working Minutes Excluding Weekends and Holidays
Manish Mishra Dec 30, 2014 10:45 PM (in response to KK Molugu)Hi,well done
Your effort is appreciable(because i was also vexed up withh similar problem last week,although i got solution now,will share this week).
But few things which I found as a limitation/flaws in this solution.(may be i am wrong,please clarify)
1.Do you find it feasible to take list of holidays as a string parameter.
What if I have a long list of holidays of 45 years in an another sheet.
2.How to related 2 sheets(ie. 'holiday list' and 'employee work tenure')..Is that blending/joins/some other way
How it works if a new employee enter in the dataset with a date range that is not found around holidaydates.(How to make it dyanamic?)
3.A caution should be taken while calculating actual holiday because there may be few holidays which falls on sat sun,so in that way, countable holidays=[all holidays in date range]  [Exclude holidays which falls on weekends]
because all weekends taken out already.
4.What if employee of certain region(if region is also a column) follows weekends as fri and sat(instead of satsun),
does the same calculated field to exclude weekends will work here.(although i think case/when/then will be a better approach here sth like when region is "gulf " weekend=......when "rest others" then weekend=....(formula what u wrote).....)
Thanks,
manish

9. Re: How to Calculate Working Minutes Excluding Weekends and Holidays
Łukasz Majewski Jan 10, 2015 2:16 PM (in response to Felix Caspari)Some time ago I needed such a calculation so I created a SQL function attached (WorkTime4).
It is tsql (MS SQL Server) but can be translated to another target, I guess. It takes a bunch of parameters:
meaning:
 @t1 your start datetime
 @t2 your end datetime
 @weekstart time work starts on weekdays
 @weekend time work ends on weekdays
 @Satstart time work starts on Saturday
 @Satend time work ends on Saturday
 @Sunstart time work starts on Sunday
 @Sunend time work ends on Sunday
 @Saturday first day of weekend (defaults to 6 <Saturday>)
 @Sunday second day of weekend (defaults to 7)
Weekend start & end times default to 00:00 meaning no business on those days. If you put NULL into any start/end parameters it will mean 24 hour workday.
The result is seconds (integer). When aggregating long time spans casting into bigint is recommended to avoid overflow.
The function does not take additional holidays parameters but it may be worked around. You may join your [dates] table with [holidays] table so that you split the date range with holidays, calculate these new individual time spans and sum them up.
I created a random set of dates & holidays and joined them like this:
select x.r, x.date1, x.date2, x.[start on], x.[end on], x.[Working Seconds], x.[Working Minutes], x.[Working Days],
SUM(case when x.rr=1 then dbo.WorkTime4(x.date1,case when x.rrr=1 then x.date2 else x.holiday end,'08:00','18:00',default, default, default, default, x.Saturday, x.Sunday) else 0 end
+case when x.holiday is null then 0 else dbo.WorkTime4(case when x.holiday=x.date2 then x.date2 else dateadd(day,1,x.holiday) end ,case when x.rrr=1 then x.date2 else y.holiday end,'08:00','18:00',default, default, default, default, x.Saturday, x.Sunday) end) [WorkSec excl Holidays],
SUM(case when x.rr=1 then dbo.WorkTime4(x.date1,case when x.rrr=1 then x.date2 else x.holiday end,'08:00','18:00',default, default, default, default, x.Saturday, x.Sunday) else 0 end
+case when x.holiday is null then 0 else dbo.WorkTime4(case when x.holiday=x.date2 then x.date2 else dateadd(day,1,x.holiday) end ,case when x.rrr=1 then x.date2 else y.holiday end,'08:00','18:00',default, default, default, default, x.Saturday, x.Sunday) end)/60. [WorkMin excl Holidays],
SUM(case when x.rr=1 then dbo.WorkTime4(x.date1,case when x.rrr=1 then x.date2 else x.holiday end,'08:00','18:00',default, default, default, default, x.Saturday, x.Sunday) else 0 end
+case when x.holiday is null then 0 else dbo.WorkTime4(case when x.holiday=x.date2 then x.date2 else dateadd(day,1,x.holiday) end ,case when x.rrr=1 then x.date2 else y.holiday end,'08:00','18:00',default, default, default, default, x.Saturday, x.Sunday) end)/10.0/3600 [WorkDay excl Holidays]
from
(
select r,date1, date2, h.holiday, DATENAME(weekday,date1) [start on], DATENAME(weekday,date2) [end on], Saturday, Sunday,
dbo.WorkTime4(date1, date2,'08:00','18:00',default, default, default, default, Saturday, Sunday) [Working Seconds],
dbo.WorkTime4(date1, date2,'08:00','18:00',default, default, default, default, Saturday, Sunday)/60.0 [Working Minutes],
dbo.WorkTime4(date1, date2,'08:00','18:00',default, default, default, default, Saturday, Sunday)/10/3600.0 [Working Days]
, ROW_NUMBER() over (partition by d.r order by h.holiday asc) rr, ROW_NUMBER() over (partition by d.r order by h.holiday desc) rrr
from (select *, ROW_NUMBER() over (order by (select 1)) r from dates$ )d
left join holidays$ h ON h.holiday between d.date1 and d.date2
) x
left join
(
select r,date1, date2, h.holiday, DATENAME(weekday,date1) [start on], DATENAME(weekday,date2) [end on], Saturday, Sunday,
dbo.WorkTime4(date1, date2,'08:00','18:00',default, default, default, default, Saturday, Sunday) [Working Seconds],
dbo.WorkTime4(date1, date2,'08:00','18:00',default, default, default, default, Saturday, Sunday)/60.0 [Working Minutes],
dbo.WorkTime4(date1, date2,'08:00','18:00',default, default, default, default, Saturday, Sunday)/10/3600.0 [Working Days]
, ROW_NUMBER() over (partition by d.r order by h.holiday asc) rr
from (select *, ROW_NUMBER() over (order by (select 1)) r from dates$ )d
left join holidays$ h ON h.holiday between d.date1 and d.date2
) y
ON x.r=y.r and x.rr=y.rr1
GROUP BY x.r, x.date1, x.date2, x.[start on], x.[end on], x.[Working Seconds], x.[Working Minutes], x.[Working Days]
Note I used date ranges all starting/ending at midnight.
Oh and I used '08:00' as start time rather than '09:00' therefore division by 10 in "days" column...

WorkTime.xlsx 28.8 KB

WorkTime4.sql.zip 1.4 KB

10. Re: How to Calculate Working Minutes Excluding Weekends and Holidays
Łukasz Majewski Dec 9, 2016 12:41 AM (in response to Łukasz Majewski)I had a second thought on this and want to share a much simpler SQL yielding exactly the same results:
select d.date1, d.date2, d.Saturday, d.Sunday
, max(dbo.WorkTime4(date1, date2,'08:00','18:00',default, default, default, default, Saturday, Sunday)) [Work Time]
, sum(dbo.WorkTime4(
case when h.holiday=cast(d.date1 as date) then d.date1 else h.holiday end,
case when h.holiday=cast(d.date2 as date) then d.date2 else dateadd(day,1,h.holiday) end,
'08:00','18:00', default, default, default, default, Saturday, Sunday)) [Holiday Time]
, max(dbo.WorkTime4(date1, date2,'08:00','18:00',default, default, default, default, Saturday, Sunday))

sum(case when h.holiday is not null then dbo.WorkTime4(
case when h.holiday=cast(d.date1 as date) then d.date1 else h.holiday end,
case when h.holiday=cast(d.date2 as date) then d.date2 else dateadd(day,1,h.holiday) end,
'08:00','18:00', default, default, default, default, Saturday, Sunday) else 0 end) [The Work Time]
from dates$ d left join holidays$ h ON h.holiday between cast(d.date1 as date) and cast(d.date2 as date)
group by d.date1, d.date2, d.Saturday, d.Sunday
So this time the idea is to subtract holiday time from overall work time using the same function.
_______________________________________________________________________________________
I have also made this calculation in Tableau.although I recommend doing this in data source as above.
So the case is this: calculate work time in seconds taking into account work hours, weekends and holidays from:
 table of datetime ranges ([date1]  [date2])
 table of holidays ([holiday])
These are two separate tables with arbitrary number of records.
In order to achieve this one must connect Tableau workbook to the two tables as LEFT OUTER JOIN (preferred) or CROSS JOIN (last resort). I do not believe this can be achieved by "data blending" in tableau.
In the attached sample workbook I am using Excel sheets as source joined like this:
The result looks like this:
I used the same data as in my previous post so your weekend days are specified along with datetime ranges. To allow work hours specification I used these parameters:
They are integers where two left digits stand for hours (HH) and the other two minutes (mm). Hence 1800 is '18:00', 800 is '08:00'  you get the idea. Unfortunately Tableau lacks 'time' datatype...
I have also included sheets with cross join solutions but the one in 'Holidays (left join)' is the simplest and most effective.
EDIT: attached a 9.0 twbx with corrected calculations  as spotted here by himhim chan  previously it was correct if full hours were used in start/end times parameters; now it should be fine.

WorkTime.xlsx 31.7 KB

11. Re: How to Calculate Working Minutes Excluding Weekends and Holidays
kettan Jan 11, 2015 11:33 AM (in response to Felix Caspari)If your source is Excel, then you could calculate holidays there as shown in Re: How to caliculate public holidays ?

12. Re: How to Calculate Working Minutes Excluding Weekends and Holidays
Mary Solbrig Feb 19, 2015 7:04 PM (in response to Felix Caspari)This is actually not my solution but a friend of mine, but it seemed like it should be posted in the forums somewhere:
Step 0: Create two Parameters for the Start and End of Day Hours (Integer values, Step size: 1, Range: 111)
+ Day Start Hour (am)
+ Day End Hour (pm)
Step 1: Evaluate Day of week Ticket was Submitted
[!Round to Monday]=
IF DATENAME('weekday',[Ticket Submitted]) = "Saturday"
THEN DATEADD('day',2,[Ticket Submitted])
ELSEIF DATENAME('weekday',[Ticket Submitted]) = "Sunday"
THEN DATEADD('day',1,[Ticket Submitted])
ELSE [Ticket Submitted]
ENDStep 2: If it was moved to Monday, move it to the beginning of the day on Monday.
[!Round to Open]=
IF [!Round to Monday] > [Ticket Submitted]
THEN DATEADD('minute',INT([Day Start Hour (am)]*60),DATETRUNC('day',[!Round to Monday]))
ELSEIF [!Round to Monday] <
DATEPARSE("MM/dd/yyy HH:mm", STR(MONTH([!Round to Monday]))+"/"+STR(DAY([!Round to Monday]))+"/"+STR(YEAR([!Round to Monday]))+" "+STR([Day Start Hour (am)])+":00")
THEN DATEADD('minute',INT([Day Start Hour (am)]*60),DATETRUNC('day',[!Round to Monday]))
ELSE
[Ticket Submitted]
ENDStep 3: Find difference in minutes between ticket submission time and end of day.
IF minutes between !Round to Open and close of day is less than zero, THEN: 0 (ticket submitted after EOD)
ELSE: The difference in minutes between the ticket submission time and the end of day
[!SubmitTimeFirstDay]=
IF
DATEDIFF('minute',
[!Round to Open],
DATEPARSE("MM/dd/yyy HH:mm", STR(MONTH([!Round to Open]))+"/"+STR(DAY([!Round to Open]))+"/"+STR(YEAR([!Round to Open]))+" "+STR([Day End Hour (pm)]+12)+":00"))
< 0 THEN 0
ELSE
DATEDIFF('minute',
[!Round to Open],
DATEPARSE("MM/dd/yyy HH:mm", STR(MONTH([!Round to Open]))+"/"+STR(DAY([!Round to Open]))+"/"+STR(YEAR([!Round to Open]))+" "+STR([Day End Hour (pm)]+12)+":00"))
ENDStep 4: Take difference in minutes between start of day and First Response.
[!ResponseTimeLastDay]=
DATEDIFF('minute',
DATEPARSE("MM/dd/yyy HH:mm", STR(MONTH([First Response]))+"/"+STR(DAY([First Response]))+"/"+STR(YEAR([First Response]))+" "+STR([Day Start Hour (am)])+":00"),
[First Response])Step 5: Multiply the number of business days in between adjusted time and first response by the number of hours worked in a day.
[!Minutes_in_Between] =
(DATEDIFF('week',[!Round to Open],[First Response])*5
+
MIN(DATEPART('weekday',[First Response]),6)

MIN(DATEPART('weekday',[!Round to Open]),6)
1)
*
INT((12[Day Start Hour (am)])+[Day End Hour (pm)])*60Step 6: Add values from Steps 35.
Sum absolute values, to not penalize for 'negative' minutes resulting from responding before start of day.
Keep Possible negative values for the !Minutes_in_Between, to correctly calculate tickets that were responded to on the same day they were submitted.
[Total Time] =
ABS([!SubmitTimeFirstDay]) + ([!Minutes_in_Between]) + ABS([!ResponseTimeLastDay])

Response_Time_calcs.twbx 64.4 KB


13. Re: How to Calculate Working Minutes Excluding Weekends and Holidays
Łukasz Majewski Feb 20, 2015 2:06 AM (in response to Mary Solbrig)Two notes on Mary's friend's solution:
1. It seems to be an algorithm specialized for those tickets response time since in case when response happens after [End of Day Hour] it still counts the minutes between [end of day] and [response time]  see ticket name "I"
2. Due to string/date conversion & parsing, in case of large data set, the calculation takes longer than if it was integer based only:
Apart from that it looks to be right.
Attached are your and my solutions with the same data  enlarged with random dates to about 200000 rows.

Response_Time_calcs.twbx 1.3 MB

14. Re: How to Calculate Working Minutes Excluding Weekends and Holidays
vamsi potti Apr 13, 2015 12:58 AM (in response to KK Molugu)Hi,
The calculation which is provided by you is working fine, however I am trying to add an holiday which is on 3rd of April 2015, I have used the same formula (Extended accordingly), however few tickets were raised on 3rd of April which should not be a part of calculation, I mean the formula should exclude the holidays and it is not happening, kindly help me on the formula what need to be update in "holiday string" and "holiday" calculated field.
Regards,
Vamsi.