13 Replies Latest reply on Feb 10, 2017 8:03 PM by Shinichiro Murakami

# Actually I found a better way and post another thread in below place.

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

[Table-1] : Adjust Start date and End Date

Then Calculate "Days of Weekends"

Table-2 : Calculate Days between in each pattern

[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 Table-2.

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

• ###### 1. Re: EXCEL's "networkdays" s alternative

Nice explanation.

Amazing job as usual.

1 of 1 people found this helpful
• ###### 2. Re: EXCEL's "networkdays" s alternative

Luciano,

Thank you for your kind words,

Not sure this is the best way, hopefully near future version will equip "networkdays" as formula.

Thanks,

Shin

• ###### 3. Re: EXCEL's "networkdays" s alternative

Recently i had to do something similar and i solved out of Tableau because i had to consider local holidays.

I just built a calendar with a sequence and on weekends and holidays this sequence isn't incremented. using this with two left joins or data blends(intial and end date) i can subtract final - initial sequence to obtain networkdays.

It works fine but i have an extra job.

• ###### 4. Re: EXCEL's "networkdays" s alternative

Luciano

Thank you for the info.

To handle local Holidays, using another table is definitely needed.

Maybe similar to this solution.

The power of NETWORKDAYS with Tableau

Thanks,

Shin

• ###### 5. Re: EXCEL's "networkdays" s alternative

Hi,

Some time ago I implemented an equivalent of excel's NETWORKDAYS.INTL where you can specify what the weekend days are - Re: DateDiff for Working days only

Solution with holidays left joined from a separate table also included.

btw - there is a KB solution which - in my opinion - returns wrong results... here http://kb.tableau.com/articles/knowledgebase/calculating-the-number-of-business-days  which I mentioned here Re: FAQ:  Holidays & Workdays

Cheers,

Łukasz

• ###### 6. Re: EXCEL's "networkdays" s alternative

Lukasz,

Thank you for the info.  Looks like the link you attached is same one I mentioned in the post.

The FAQ page has too many link,  that may resulted in "Mixture of wheat and chaff".

Yes, these knowledge should be summarized at somewhere as FAQ type of link.

But sometimes I feel FAQ is kind of "Too much spoils, too little is nothing."

"Tag" is better?  Not sure what is the best idea.

The best knowledge basis for me is still in my head or in my bookmarks.

Thanks,

Shin

• ###### 7. Re: EXCEL's "networkdays" s alternative

Luciano,

By the way, it's little bit late, but

Congrats for reaching "Data Monarch"

Thanks,

Shin

• ###### 8. Re: EXCEL's "networkdays" s alternative

Thanks Shin.

Usually i don't mind about this kind of glory but this made me much happy, because i'm a self-learner and i learned a lot doing it.

• ###### 9. Re: EXCEL's "networkdays" s alternative

All,

Can the above be altered to show the diff in hours (excluding weekends)?

Thanks!

• ###### 10. Re: EXCEL's "networkdays" s alternative

The easy answer is just multiply "24" x number of days.

If you have working hours per day, [ie 9:00am to 5:00pm without considering lunch break], multiple "8" x days.

For the first day and the last day, there requires special handling, though.

Thanks,

Shin

• ###### 11. Re: EXCEL's "networkdays" s alternative

Tableau Community, please move this to the TabWiki, it does not belong in this forum section.  I cannot move it.   Thanks

• ###### 12. Re: EXCEL's "networkdays" s alternative

I want to do it, too.

Shin

• ###### 13. Re: EXCEL's "networkdays" s alternative

Actually I found better way and post another thread in below place.

Thanks,

Shin