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

    EXCEL's "networkdays" s alternative

    Shinichiro Murakami

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

      f:id:skn_0410:20161206080929p:plain

       

      Before going to the detail step, let me who the concept.

      1st STEP is Set Adjusted Start and End date

      f:id:skn_0410:20161206081152p:plain

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

      Then Calculate "Days of Weekends"

      Table-2 : Calculate Days between in each pattern

       

      Step-1 : 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 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