KNOWLEDGE BASE

Calculating the Number of Business Days in a Month


Published: 17 Feb 2017
Last Modified Date: 10 Sep 2017

Question

How to calculate the number of business days in a month.

 

Environment

Tableau Desktop

Answer

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, then click OK:
    • Name the calculated field. In the example workbook, the calculated field is named Start Date.
    • In the formula field, create a calculated field similar to the following: 
             MIN([Order Date])
  4. Select Analysis > Create Calculated Field again.
  5. In the Calculated Field dialog box, do the following, then click OK:
    • Name the calculated field. In the example workbook, the calculated field is named End Date.
    • In the formula field, create a calculated field similar to the following:
         MAX([Order Date])
  6. Select Analysis > Create Calculated Field a third time.
  7. In the Calculated Field dialog box, do the following, then click OK:
    • Name the calculated field. In the example workbook, the calculated field is named Number of Weekdays.
    • In the formula field, create a calculated field similar to the following:
               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 2015).
  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.
A screenshot of the view shows the number of business days - defined as non-weekend days but including holidays - for each month in the particular year.

 


Include Holidays when Calculating the Number of Business Days in a Month

Using calculated fields, follow the steps below:

Step 1: Create Calculated Field

  1. Download and open the Calculate Business Days workbook and click the Month with Holidays worksheet.
  2. Select Analysis > Create Calculated Field.
  3. In the Calculated Field dialog box, do the following, then click OK:
    • Name the calculated field. In the example workbook, the calculated field is named Number of Holidays in Range.
    • In the formula field, create a calculated field similar to the following:
            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. Select Analysis > Create Calculated Field again.
  5. In the Calculated Field dialog box, do the following, then click OK
    • Name the calculated field. In the example workbook, the calculated field is named Number of Weekdays - Holidays.
    • In the formula field, create a calculated field similar to the following:
           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 view shown below lists the number of business days and holidays in a month.

Did this article resolve the issue?