
1. Re: DateDiff for Working days only
Alex Kerin May 15, 2012 8:41 AM (in response to sinadashtipour)1 of 1 people found this helpful 
2. Re: DateDiff for Working days only
Alex Kerin May 15, 2012 10:13 AM (in response to Alex Kerin)There is also a calculation I have created  I'm sure it could be made more simple with some clever thought, but given a [date] and [start date], we first work out the day of the week of these:
[Weekday Start] datepart('weekday',[Start Date])
[Weekday End] datepart('weekday',[Date])
First the whole weeks we must add (whole weeks times 5)
int(([Date][Start Date]1)/7)*5
plus the extra days, wholly dependent on the day of week of start and end:
case [Weekday End] when 1 then
if [Weekday Start]>5 then 7[Weekday Start]
elseif [Weekday Start]=5 or [Weekday Start]=4 then 2
else 6[Weekday Start] end
When 2 then
if [Weekday Start]=7 or [Weekday Start]=1 then 1
elseif [Weekday Start]=5 or [Weekday Start]=6 then 2
else 7[Weekday Start] end
When 3 then
if [Weekday Start]>=3 and [Weekday Start]<=5 then 8[Weekday Start]
else 2 end
When 4 then
if [Weekday Start]=4 or [Weekday Start]=5 then 9[Weekday Start]
elseif [Weekday Start]=3 then 2
else 3 end
When 5 then
if [Weekday Start]=4 or [Weekday Start]=3 then 6[Weekday Start]
elseif [Weekday Start]=5 then 5
else 4 end
When 6 then
if [Weekday Start]=5 or [Weekday Start]=4 then 7[Weekday Start]
elseif [Weekday Start]=2 or [Weekday Start]=3 then 4
else 5 end
When 7 then
if [Weekday Start]=6 or [Weekday Start]=5 then 7[Weekday Start]
elseif [Weekday Start]=3 or [Weekday Start]=4 then 3
elseif [Weekday Start]=2 then 4
else 5 end
end
This gives exactly the same answers as networkdays in Excel, but with no possibility of adding holidays as well.

Networkdays.twbx.zip 48.0 KB


3. Re: DateDiff for Working days only
sinadashtipour May 16, 2012 2:08 AM (in response to Alex Kerin)Thanks. I found this working for me:
DATEDIFF("weekday",[Calculation1],[Calculation2])2*(DATEPART('week', [Calculation2]) DATEPART('week', [Calculation1]))

4. Re: DateDiff for Working days only
Alex Kerin May 16, 2012 5:06 AM (in response to sinadashtipour)Are you sure that calc is correct? I don't think it works properly, and doesn't work across years/jandec split

5. Re: DateDiff for Working days only
sinadashtipour Jul 9, 2012 6:50 AM (in response to Alex Kerin)Yes it seems to be ok. The only problem it seems to have when using dates from two different data sources (blended data). There seems to be so much restrictions when it comes to blending data. Is there a way around this problem?
Thansk

6. Re: DateDiff for Working days only
Brett Raven Jun 20, 2013 8:43 PM (in response to Alex Kerin)Hi I don't think this works perfectly yet. If you have the start and end date the same, the networkdays = 5. Needs some tweaking
Has anyone added/seen a larger calculation that takes this into account along with the times (minutes preferably) with a lookup to a holiday table or data set?

7. Re: DateDiff for Working days only
Lynn Snow Jul 30, 2014 1:23 PM (in response to Alex Kerin)2 of 2 people found this helpfulAlex,
Thanks for your post on 5/15/12 with the calculation to count workdays between two dates. Most calculations I've found use DateDiff then subtract weekends, but those aren't accurate when weekends span between two months. Yours was the most accurate calculation I've found so far, but it was slightly off, maybe because I was using it to calculate work days from the start to a midpoint in the month instead of for the entire month.
Using your calculations as a starting point, I created a new method that worked exactly for me, so I wanted to post it in case it can help anyone else. I included comments below with "//" to help explain how it works.
WeekdayStart
// Return day number in week (1=Sunday, 2=Monday, etc..., 7=Saturday)
DatePart('weekday',[Start Date])
WeekdayEnd
// Return day number in week (1=Sunday, 2=Monday, etc..., 7=Saturday)
DatePart('weekday',[End Date])
Work Days
// (1) Calculate number of whole weeks between dates, then convert to # of Work Days
INT(([End Date][Start Date])/7)*5
+
// (2) If sum of the first and last week days is a full week, then it was accounted for above,
// so we need to subtract those work days because we will calculate work days for the
// first and last weeks in the next two steps
IF
// # of total days in 1st Week
8[WeekdayStart]
+
// # of total days in Last Week
[WeekdayEnd]
> 7
// Subtract week of work days from step (1) which will be calculated in steps (3) & (4) below
THEN 5
ELSE 0
END
+
// (3) Add # of Work Days for 1st Week
// If 7 Days, then subtract 2 for Sun & Sat
IF 8[WeekdayStart] = 7 THEN 8[WeekdayStart]2
// If 1 Day and it's Sunday, then exclude
ELSEIF 8[WeekdayStart] = 1 AND [WeekdayStart] = 1 THEN 0
// Otherwise subtract the one Sunday
ELSE 8[WeekdayStart]1
END
+
// (4) Add # of Work Days for Last Week
// If 7 Days, then subtract 2 for Sun & Sat
IF [WeekdayEnd] = 7 THEN [WeekdayEnd]2
// If 1 Day and it's Sunday, then exclude
ELSEIF [WeekdayEnd] = 1 THEN 0
// Otherwise subtract the one Sunday
ELSE [WeekdayEnd]1
END

8. Re: DateDiff for Working days only
Alex Kerin Jul 29, 2014 6:53 PM (in response to Lynn Snow)Nice job Lynn.

9. Re: DateDiff for Working days only
manish mishra Jan 8, 2015 10:25 PM (in response to sinadashtipour)Hi Intellectuals,
Before going through solution I tried to make understand the requirements and solution approach.Please make me aware if found any flaws in my solution(even for specific cases).
What's the requirement/challenges n caution to take=
 We need to find business hour of an employee
 For holiday calculationcustom sql required (since blending or joining can’t performed)
 Working hour of an employee is 9AM6pm and satsun are taken as weekends here
 Business days=total days elapsed in date range  all weekends  holidays in that date range+ all those holidays which falls on sat or sun
 So,exclude sat/sun from holidays becz all weekends taken out already
 Actual holidays=[all holidays in date range]  [Exclude holidays which falls on weekends]
 Calculating missing hr means Hr. difference calculation corresponding to each start time and end time as office is b/w 96
 Business hrs means all working hours of days(exclude net holidays n weekend)
 Business hours= [(total hrs elapsed  missing hrs)]
 There is a list of holidays(although I've given this only for year 2012,You can add more dates of other years)
 Must check on aggregation (ie. sum,avg,min etc.) when taking measures in the view by dragging(why because records are kept in that way,view data to visualize this situation).
 All calculated field have given comments for better understanding(it'll tell u how it works)
So,APPROACH to accomplish tasks is=====
1>custom sql required to relate both sheets.
2>calculation of weekdays = days without weekends (take avg aggregation when dragging this measure)
3>holiday calculation in given date range
4>exclude sat/sun from holidays becz all weekends taken out already
5>find actual holidays
6>calculation of business days
7>calculation of missing hours (take min aggregation when dragging this measure)
8>calculation of business hours
9>Utilization calculation
See TWBX file for details.
my solution can also be referred on this thread
http://community.tableau.com/message/322255#322255
It's works very perfect for all cases/conditions

working hr employee.xlsx 15.4 KB

10. Re: DateDiff for Working days only
Łukasz Majewski Jan 27, 2015 5:30 AM (in response to manish mishra)Manish,
Your calculation in the first row says 93 as "business hours"  I reckon this should be 84:
simple check in excel:
My attached solution results:
Here is explanation: Re: How to Calculate Working Minutes Excluding Weekends and Holidays
# I set [Sunday] field to 6 instead of 7 by mistake  corrected and republished now

employee work time hrs.twbx 49.0 KB


11. Re: DateDiff for Working days only
manish mishra Jan 26, 2015 10:45 PM (in response to Łukasz Majewski)Well Lukasz,
I've seen your solution,very complicated fields were written although useful and highly appreciable because I may ned to use the similar formulae in my other requirements.
Anyways,this solution is very useful but in context of my raised ticket I found it not solving my requirement exactly.
Few things I want to have clarified from your side.
1)As you said
"Your calculation in the first row says 93 as "business hours"  I reckon this should be 84:"
In order to justifying what I wrote please check 1st row manually in calender (forget calculated fields for a while)
Since date range is too small that you can check this from calender.(Involve 26/3 and 9/4 also)
See Ashwin is working from 26 march to 9 april2014.So,it's clear excluding sat/sun it's exactly 11 working days.
So,11 days X 9 hrs=99 hrs (since employee works 9hrs a day ie. 9AM6PM that is fixed)
Now,consider arrival and departure time while calculating how much hr. he missed
as start time is 3PM(ie. 1500 hrs) so,he missed 6 hrs office (as he supposed to come at 9AM)
and end time 1AM,it doesn't make any difference(since leaving 1 at night is not taken in calculation as all time before 9AM and after 6PM for arriving and leaving office is not counted)
So,Ashwin in totality missed 6 hrs.
So,Business hr of Ashwin=996=93hrs.
2)Now,in your calculation let's take eg. of employee Suman
see only 1 day holiday comes in his tenure,that's fine,agreed.
So, accordingly corresponding "holiday time" would be 1day x 24 x 60 x 60=86400 seconds
but,in that place 32400 is computed as result in your twbx.
3)So,please clarify how holiday time and work time is calculated means how it can be associatted with requirement.(brief the approach)
4)Also,tell you made solution dynamic by supplying parameters
what's purpose of parameters sunend,satend,sunstart etc.

12. Re: DateDiff for Working days only
Łukasz Majewski Jan 27, 2015 1:27 AM (in response to manish mishra)1) No, it is 9 full business days and 1 with 3 hours, hence 9x9+3
2) It is subtraction of business hours during holiday from the work time calculated as if there was no holiday, hence 1x9x3600 = 32400 seconds
3) Refer to my original post that I linked
4) Those parameters come to play when there is business over weekend but with different start/end hours than on weekdays, e.g. you work 918 MonFri, 1017 Sat and 1014 Sun

13. Re: DateDiff for Working days only
Karthik Ezhilvannan Mar 30, 2016 1:35 AM (in response to Lynn Snow)Lynn,
Even your logic needs slight change.
When the start date and end date are exactly seven days apart but the value of int([end date][start date])/7=0, the logic falls.

14. Re: DateDiff for Working days only
Łukasz Majewski Apr 6, 2016 10:18 AM (in response to Karthik Ezhilvannan)Since the topic reemerged after more than a year and the problem still does not seem to be properly solved I want to share my solution. It is very similar to what I mentioned above but deals with dates (not datetime) and counts days (and not seconds).
1. Attached is a tsql function as I believe this should be calculated at the source, e.g.
select a.d1, a.d2, a.NETWORKDAYS_1, dbo.NetworkDays(d1, d2, 6,7) calc1, a.NETWORKDAYS_2, dbo.NetworkDays(d1, d2, 5,6) calc2 , MIN(dbo.NetworkDays(d1, d2, 6,7))SUM(dbo.NetworkDays(b.date, b.date, 6, 7)) Business_Days , COUNT(b.date) h_cnt, min(b.date) min_h_date, max(b.date) max_h_date, min(b.name) min_h_name, max(b.name) max_h_name from NETDAYS a left join (select * from COMREF.dbo.public_holidays where ISO_COUNTRY_CODE_SHORT='PL') b ON a.d1<=b.date and a.d2>=b.date group by a.d1, a.d2, a.NETWORKDAYS_1, dbo.NetworkDays(d1, d2, 6,7) , a.NETWORKDAYS_2, dbo.NetworkDays(d1, d2, 5,6)
2. A tableau equivalent of the row level function I created looks as follows:
As its name suggests this is supposed to be equivalent of excel NETWORKDAYS.INTL and you can specify the weekend days with a parameter.
3. In order to be able to exclude holidays we need a table with holiday dates which we will LEFT JOIN with our primary data. In this example I am using data from excel file but Tableau only allows custom sql or '<=' join operator against excel if we use 'legacy connection'...
with such prepared dataset let's introduce holidays count:
and finally the aggregated measure:
The results:
I have not tested this extensively so do let me know if you find an error.
EDIT: I realized that  due to the fact I used 8 as the value for 'none' weekend day in the parameters  the formulas would yield incorrect results if we used that value (8%7==1%7). So I modified it and republished. Also removed redundant start_of_week parameter from DATEPART function.

NetworkDaysIntl.twbx 44.5 KB

NetworkDays.sql.zip 638 bytes
