EXCEL's "networkdays" s alternative
Shinichiro Murakami Feb 22, 2017 1:28 PM02/22/2017 update:
Actually I found a better way and post another thread in below place.
ALL PLACES > VIZ TALK > TABLEAU COMMUNITY LIBRARY > TABLEAU WORKBOOK LIBRARY > DOCUMENTS
Excel's Networkdays Alternative [version 2]
Hi Tableau Lovers
It's getting colder and colder.
I don't like long winter in North West part of U.S...
I got question on the forum and already answered, but I think I'd better document something because there may be many similar requests or needs.If there are already some discussions regarding "networkdays", please let me know.
Here I only focus on "How to exclude weekends from number of days between two points of Date".
It means I am not trying to solve "National Holiday Calendar" type of things, purely focus on excluding weekends.
The target is to replace below Excel's calculations with Tableau's functions.
Before going to the detail step, let me who the concept.
1st STEP is Set Adjusted Start and End date
[Table1] : Adjust Start date and End Date
Then Calculate "Days of Weekends"
Table2 : Calculate Days between in each pattern
Step1 : Calculate Adjusted Date
[First Friday from Start]
date(datetrunc('week',[Start Date]+4,"Friday"))
[Last Monday from End]
date(datetrunc('week',[End Date]+2,"Monday"))
With this adjustment, we understand the days between really includes weekend or not.
Plus and how many weekends included.
The number of shifted days are differed by the weekday of start date(/end date).
But the point here is only weekend, it goes opposite way compared with weekday's change.
Start Date always resulted in certain Friday, and End date always resulted in a certain Monday.
Then calculation to count weekend days becomes little bit easier.
I prepared some patterns and you can see the difference on Table2.
Final Calculation is like below, but you can have better understanding with looking thru the table and attached excel file.
[Weekend Days]
min(
[Days Between],
(floor(([Last Monday from End][First Friday from Start])/7)+1)*2
if datename('weekday',[Start Date])="Sunday" then 1 else 0 end
if datename('weekday',[End Date])="Saturday" then 1 else 0 end
)
[NETWK Days Calculated]
[Days Between][Weekend Days]
[NETWK Days by one field]
max(0,date([End Date])date([Start Date])+1)

min(
max(0,date([End Date])date([Start Date])+1),
(floor((date(datetrunc('week',[End Date]+2,"Monday"))
date(datetrunc('week',[Start Date]+4,"Friday")))/7)+1)*2
if datename('weekday',[Start Date])="Sunday" then 1 else 0 end
if datename('weekday',[End Date])="Saturday" then 1 else 0 end
)
Again if you have any better idea, please let me know.
Here is another method to use date table.
The power of NETWORKDAYS with Tableau
Another solution
Re: DateDiff for Working days only
And here is an idea post to implement the function in some future version.
https://community.tableau.com/ideas/3653
Enjoy Tableau!
Thanks,
Shin

Networkdays.xlsx 38.9 KB

Tableau_Networkdays_SM_10.0.twbx 56.6 KB