1 2 Previous Next 16 Replies Latest reply on Nov 8, 2016 8:52 PM by Derek Arsenault

How to Calculate Working Minutes- Excluding Weekends and Holidays

Hello,

Any help on how to calculate the working minutes, which is a time difference between two (02) date, taking into account the Week day (Monday-Friday), bank holidays, and working business hours (9:00 - 18:00)

Date 1: X1, format: DD/MM/YYYY hh:mm:ss

Date 2: X2, format: DD/MM/YYYY hh:mm:ss

Business Hours: 8:00 - 18:00

List of Bank Holidays: January 1st, May 1st, May 8th...etc

• 2. Re: How to Calculate Working Minutes- Excluding Weekends and Holidays

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

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 "re-posting" the question.

Thanks,

Felix

• 4. Re: How to Calculate Working Minutes- Excluding Weekends and Holidays

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

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

Did that help Felix...

• 7. Re: How to Calculate Working Minutes- Excluding Weekends and Holidays

Plz refer the attached workbook and related excel sheet(as data source).

In that working hour of an employee is 9AM-6pm,

sat-sun 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 9-6 (total hrs-missing 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.15-9)

again,at end date employee was expected to start work at 6pm while he ended at 10am so again he missed 8hrs(ie.18-10).

so,total missing is 6+8=14hrs.

although if both start n end time is not in range of 9-6 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)

http://community.tableau.com/message/324381#324381

• 8. Re: How to Calculate Working Minutes- Excluding Weekends and Holidays

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 4-5 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 data-set with a date range that is not found around holiday-dates.(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 sat-sun),

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

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

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

• 10. Re: How to Calculate Working Minutes- Excluding Weekends and Holidays

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.

• 11. Re: How to Calculate Working Minutes- Excluding Weekends and Holidays

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

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: 1-11)

+ 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]
END

Step 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]
END

Step 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"))
END

Step 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)])*60

Step 6:  Add values from Steps 3-5.

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])

2 of 2 people found this helpful
• 13. Re: How to Calculate Working Minutes- Excluding Weekends and Holidays

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.

• 14. Re: How to Calculate Working Minutes- Excluding Weekends and Holidays

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.

1 2 Previous Next