KNOWLEDGE BASE

Calculating the Number of Business Days


Product(s): Tableau Desktop
Version(s): 8.3, 8.2, 8.1
Last Modified Date: 16 Aug 2016

This article demonstrates multiple ways to calculate the number of business days in a specific period of time, and demonstrates how you can display only business days in a view.

Calculate the Number of Business Days in a Month

Follow the steps below to calculate the number of business days in a month using calculated fields.

Step 1: Create Calculated Fields

  1. Download and open the Calculate Business Days workbook and then click the Month No Holidays worksheet.
  2. Select Analysis > Create Calculated Field.
  3. In the Calculated Field dialog box, do the following and then click OK:
    1. Name the calculated field. In the example workbook, the calculated field is named Start Date.
    2. In the formula field, create a calculated field similar to the following:
    3. MIN([Order Date])

  4. Select Analysis > Create Calculated Field again.
  5. In the Calculated Field dialog box, do the following and then click OK:
    1. Name the calculated field. In the example workbook, the calculated field is named End Date.
    2. In the formula field, create a calculated field similar to the following:
    3. MAX([Order Date])

  6. Select Analysis > Create Calculated Field a third time.
  7. In the Calculated Field dialog box, do the following and then click OK:
    1. Name the calculated field. In the example workbook, the calculated field is named Number of Weekdays.
    2. In the formula field, create a calculated field similar to the following:
    3. DATEDIFF("weekday", [Start Date], [End Date])

      - 2 * (DATEPART('week', [End Date]) -DATEPART('week', [Start Date]))

      + (IF DATENAME('weekday',[End Date]) = 'Saturday' OR DATENAME('weekday',[Start Date]) = 'Sunday'

      THEN 0 ELSE 1 END)

Step 2: Build the View

  1. From Dimensions, drag Order Date to the Rows shelf.
  2. On the Rows shelf, right-click Order Date, and select Month (May 2011).
  3. On the Rows shelf, right-click Order Date again, and select Discrete.
  4. From Measures, drag Number of Weekdays to Text on the Marks card.

The following view shows the number of business days for each month, including holidays.

Include Holidays

Follow the steps below to calculate the number of business days in a month, including holidays, using calculated fields.

Step 1: Create Calculated Fields

  1. Complete the steps in Step 1 of Calculate the Number of Business Days in a Month, and then do the following.
  2. Download and open the Calculate Business Days workbook, and then click the Month with Holidays worksheet.
  3. Select Analysis > Create Calculated Field.
  4. In the Calculated Field dialog box, do the following and then click OK:
    1. Name the calculated field. In the example workbook, the calculated field is named Number of Holidays in Range.
    2. In the formula field, create a calculated field similar to the following:
    3. 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

  5. Select Analysis > Create Calculated Field again.
  6. In the Calculated Field dialog box, do the following and then click OK:
    1. Name the calculated field. In the example workbook, the calculated field is named Number of Weekdays - Holidays.
    2. In the formula field, create a calculated field similar to the following:
    3. DATEDIFF("weekday",[Start Date],[End Date])

      - 2 *

      (DATEPART('week', [End Date]) -DATEPART('week', [Start Date]))

      + 1 - [Number of Holidays]

Step 2: Build the View

  1. From Dimensions, drag Order Date to the Filters shelf.
  2. In the Filter Field dialog box, select Years, and then click Next.
  3. In the Filter dialog box, select 2013, and then click OK.
  4. From Dimensions, drag Order Date to the Rows shelf.
  5. On the Rows shelf, right-click Order Date, and select Month (May 2011).
  6. On the Rows shelf, right-click Order Date again, and select Discrete.
  7. Select Data > Edit Relationships.
  8. In the Relationships dialog box, click Add.
  9. For Primary data source field, select Order Date.
  10. For Secondary data source field, select Date, and then click OK twice.
  11. In the Data pane, select holidays 2013.xlsx, and then link the data sources on the Date field by clicking the link icon in the Dimensions pane.
  12. In the Data pane, select Sample - Superstore Subset (Excel).
  13. From Measures, drag Start Date and End Date to the right of Order Date on the Rows shelf.
  14. From Measures, double-click Number of Holidays in Range and Number of Weekdays - Holidays to add them to the view. Measure Values will automatically be placed on Text on the Marks card, and Measure Names will automatically be placed on the Columns shelf.

The following view shows the number of business days and holidays in a month.

 

Calculate the Number of Business Days Between Two Dates

Follow the steps below to calculate the number of business days between two dates using a calculated field. Download and open the Calculate Business Days Two Dates workbook to follow the steps below.

Step 1 

In Tableau Desktop, select Analysis > Create Calculated Field.

Step 2 

In the Calculated Field dialog box that opens, enter a name for the field.

In the formula field, type the following, and then click OK:

DATEDIFF('week',[<Start Date>],[<End Date>])*5

+

MIN(DATEPART('weekday',[<End Date>]),6)

-

MIN(DATEPART('weekday',[<Start Date>]),6)

Where <Start Date> and <End Date> are the names of date fields in the worksheet.

 

Display Only Business Days

Follow the steps below to show only business days using a calculated field.

Step 1: Create the View

  1. Download and open the Filter to Business Days workbook.
  2. From Dimensions, drag Order Date to the Rows shelf.
  3. Right-click Order Date on the Rows shelf and select More > Custom.
  4. In the Custom Date dialog box, do the following and then click OK:
    • For Detail, select Month / Day / Year.
  5. From Dimensions, drag Order Date to the Rows shelf again.
  6. Right-click the new Order Date on the Rows shelf, and select More > Weekday.

Step 2: Create the Calculated Field

  1. Select Analysis > Create Calculated Field.
  2. In the Calculated Field dialog box , enter a name for the field. In the Filter to Business Days example workbook, the field is named Is Business Day?.
  3. In the formula field, create a calculated field similar to the following, and then click OK:
  4. DATEPART('weekday',[Order Date]) <> 1 AND DATEPART('weekday',[Order Date]) <> 7

     

    Tableau Desktop assigns the following numerical values to days of the week:

    Day Numerical Value
    Sunday1
    Monday2
    Tuesday3
    Wednesday4
    Thursday5
    Friday6
    Saturday7

     

    To exclude Saturday and Sunday from the Order Date field, type 1 for Sunday and 7 for Saturday in your calculated field.

     

  5. From Dimensions, drag the newly created calculated field to the Filters shelf.
  6. In the Filter dialog box that opens, select True to exclude Saturday and Sunday from the date field and the view, and then click OK.

 

Alternate Search Terms:Business Days, Business, Holidays, Display dates, Times, Calculated Fields, Month, Date
Did this article resolve the issue?