3 Replies Latest reply on Jul 1, 2014 6:38 AM by David Timmerman

    Net Working Day Calculation?

    Xiaojun Wu

      How could I know how many net working days between two days?

       

      Thanks

        • 1. Re: Net Working Day Calculation?
          Dan Sanchez

          Hello Xiaojun,

           

           

          We have a couple options available when finding network days and each of the options will depend on the use case.  Each of the below options will be using the Superstore data set that comes packaged with Tableau Desktop.

           

           

          Option 1: Calculating the number of business days in a month (not excluding holidays)

          1. Select Analysis > Create Calculated Field

          2. In the Calculated Field dialog box that opens, name the calculated field start date

          3. Enter the formulas as below and click OK:

           

            MIN([Order Date])

           

          4. Select Analysis > Create Calculated Field

          5. In the Calculated Field dialog box that opens, name the calculated field end date

          6. Enter the formulas as below and click OK:

           

            MAX([Order Date])

           

          7. Select Analysis > Create Calculated Field

          8. In the Calculated Field dialog box that opens, name the calculated field number of weekdays

          9. Enter the formulas as below and click OK:

           

            DATEDIFF("weekday", [start date], [end date])

            - 2 *

            (DATEPART('week', [end date]) -DATEPART('week', [start date]))

            + 1

           

           

           

          Option 2: Calculating the number of business days in a month (excluding holidays)

          This solution requires that the list of holidays for the year be contained within a separate data source.  For my example I have a list of holidays in 2013.  After connecting to both Superstore and the 'holiday 2013.xlsx' file, perform the same steps in Option 1, and then the following additional steps:

          1. Select Analysis > Create Calculated Field

          2. In the Calculated Field dialog box that opens, name the calculated field number of holidays in range

          3. Enter the formulas as below and click OK:

           

            IF MIN([holidays 2013.xlsx].[Date]) >= [start date]

            AND

            MIN([holidays 2013.xlsx].[Date]) <= [end date]

            THEN SUM([holidays 2013.xlsx].[Number of Records])

            END

           

          4. Click OK

          5. Select Analysis > Create Calculated Field

          6. In the Calculated Field dialog box that opens, name the calculated field number of weekdays - holidays

          7. Enter the formulas as below and click OK:

           

            DATEDIFF("weekday", [start date], [end date])

            - 2 *

            (DATEPART('week', [end date]) -DATEPART('week', [start date]))

            + 1 -

            [number of holidays in range]

           

          8. Click OK

           

           

          Please note that both of the above solutions can be implemented when the data set contains values for every day of the month.  In the attached workbook I have also created a "v2" copy of each of the calculated fields.  The "v2" copy can be used when the data set does not contain values for each day in the month.

          • 2. Re: Net Working Day Calculation?
            Xiaojun Wu

            Hi Dan,

             

            Just checked the option 1. If we use June 2014 as example. There are 21 business days (not excluding holidays). The option 1 fomular will give only 20 days. In option 1, do we have to consider week start day (Sunday or Monday)

             

            For option 2.  Here is what I am thinking: If we have to bring excel or other data source, we could do calculation in the other datasource, blend with Tableau data and bring the net working day. What do you think?

             

            Thanks for your fomular and hope you could do help me understand the calculation difference of option 1.

             


            • 3. Re: Net Working Day Calculation?
              David Timmerman

              Please also consider voting for this in Ideas (adding a NETWORKDAYS function):

               

              http://community.tableau.com/ideas/1503#comment-7045