KNOWLEDGE BASE

Calculating Number of Weekdays Between Dates


Published: 17 Feb 2017
Last Modified Date: 20 Apr 2018

Question

How to count the number of weekdays, or business days, between two dates.
For example, the number of business days between two date fields in the data source, two parameter dates, or the number of weekdays within a month.

Environment

Tableau Desktop

Answer

    
         
CLICK TO EXPAND SOLUTION         

Option 1: Count number of weekdays between two date fields in the data source

Step 1: Create Calculated Fields

  1. Select Analysis > Create Calculated Field
  2. In the Calculated Field dialog box that opens, do the following, and then click OK:
    1. Name the calculated field. In this example, the calculated field is named "Order Date (shifted to weekday)"
    2. In the formula field, create a calculation similar to the following:
       
      IF DATEPART('weekday', [Order Date]) = 1
      THEN DATEADD('day', 1, [Order Date])
      ELSEIF DATEPART('weekday', [Order Date]) = 7
      THEN DATEADD('day', 2, [Order Date])
      ELSE [Order Date]
      END
  3.  Create a calculated field with a name like "Ship Date (shifted to weekday)" with a calculation similar to the following:
     
    IF DATEPART('weekday', [Ship Date]) = 1
    THEN DATEADD('day', -2, [Ship Date])
    ELSEIF DATEPART('weekday', [Ship Date]) = 7
    THEN DATEADD('day', -1, [Ship Date])
    ELSE [Ship Date]
    END
  4.  Create a calculated field with a name like "# of Weekdays from Order to Shipping" with a calculation similar to the following:
     
    MIN(
    DATEDIFF('day', [Order Date (shifted to weekday)], [Ship Date (shifted to weekday)])
    + 1
    - 2 * DATEDIFF('week', [Order Date (shifted to weekday)], [Ship Date (shifted to weekday)])
    )

Step 2: Build the View

  1. Drag [Order ID] to the Row shelf
  2. Optional: drag [Order Date] and [Ship] Date to the Rows shelf
  3. Drag [# of Weekdays from Order to Shipping] to Text on the Marks card
 
CLICK TO EXPAND SOLUTION        

Option 2: Count number of weekdays each month for one date field         

This example uses the sample data set Superstore to find the number of weekdays within each month for order date.
  1. Select Analysis > Create Calculated Field
  2. In the Calculated Field dialog box that opens, do the following, and then click OK:
    1. Name the calculated field. In this example, the calculated field is named "Start of Month Date"
    2. In the formula field, create a calculation similar to the following:

      DATETRUNC('month', [Order Date])
  3. Create a calculated field with a name like "End of Month Date" with a calculation similar to the following:

    DATEADD('day', -1, DATEADD('month', 1, DATETRUNC('month', [Order Date])))
  4. Follow directions in Option 1 using [Start of Month Date] instead of [Order Date], and [End of Month Date] instead of [Ship Date] 
         
CLICK TO EXPAND SOLUTION        

Option 3: Count weekdays excluding holidays

This example uses the sample data set Superstore to find the number of weekdays between the order date and ship date for each order.

Step 1: Set up the data

  1. Add a table to original data source that contains a list of holiday dates. In this example, that table is called "Holidays"
  2. Navigate to the Data source tab in Tableau Desktop
  3. Add the "Holidays" table to the canvas area
  4. In the Join dialog, so the following:
    1. Select Left
    2. Under Data Source, select Order Date
    3. Under Holidays, select Holiday Date
    4. Click the equal sign, and select <=
    5. Under Data Source, click Add new join clause, and select Ship Date
    6. Under Holidays, select Holiday Date
    7. Click the equal sign, and select >=
 
Note: [Order Date] and [Ship Date] should be replaced with the actual start and end date respectively. If the start and end date are calculated fields, then these calculations can be recreated in join calculations. If it is not possible to add a table to the original data source with holiday dates, then create a cross-database join to an external table containing holiday dates. This requires upgrading to Tableau Desktop 10.5 or later to make use of the inequalities in join clauses in cross-database joins feature.
 

Step 2: Create the Calculations

  1. Navigate to a worksheet
  2. Follow steps 1-1 to 1-3 in the directions for option 1 to create the [Order Date (shifted to weekday)] and [Ship Date (shifted to weekday)] calculations
  3.  Create a calculated field with a name like "# of Weekdays from Order to Shipping" with a calculation similar to the following:
     
    MIN(
    DATEDIFF('day', [Order Date (shifted to weekday)], [Ship Date (shifted to weekday)])
    + 1
    - 2 * DATEDIFF('week', [Order Date (shifted to weekday)], [Ship Date (shifted to weekday)])
    ) - COUNTD([Holiday Date])


Step 3: Create the view

  1. Drag [Order ID] to the Row shelf
  2. Optional: drag [Order Date] and [Ship] Date to the Rows shelf
  3. Drag [# of Weekdays from Order to Shipping (excluding holidays)] to Text on the Marks card
Did this article resolve the issue?