When working with date fields, Tableau users can either apply mathematic operators to date fields to determine the number of days between dates, or they can use the DATEDIFF function when more control over the calculation is needed. DATEDIFF supports the ability to modify the date intervals for calculating the number of days, months or years between dates and also allows users to determine the number of days from today.
Table of Contents
Creating Calculated Fields in Tableau
To create a custom calculation in Tableau, you first need to create a Calculated Field. This will generate a new column that you can later add to various visualizations in your report. To create a new Calculated Field, either right click on an existing column from the Table Column and select Create, Calculated Field or you can right click on a column header in the data source view to perform a similar operation.
A formula box will appear where you can enter in a Tableau formula to generate a new column.
Calculating Days Between Dates
The simplest way to calculate the number of days between two dates in Tableau is to subtract the end date from the start date when creating a calculated field. If the columns are defined with a date data type Tableau will automatically return the number of days between the two dates.
The syntax for days between two dates is as follows:
[End Date] - [Start Date]The example below shows the Invoice Date being subtracted from the Shipped Date.
Column names are defined by surrounding them in square brackets. Tableau will automatically test the calculation to ensure that is valid. Click OK to accept the calculation.
The one drawback of this method is that it is very limited. The DATEDIFF formula can be used to calculate the time between two dates in different intervals.
The DATEDIFF Syntax in Tableau
To determine the number of days, weeks, months, or years between dates in Tableau users can use the DATEDIFF function. The syntax is: DATEDIFF(date_part, [Start Date], [End Date],[Start of Week]) where the date_part determines the units of the calculation, start and end dates define the calculation fields and the start of the week offsets weekly calculations.
Supported date_parts include:
- Day
- Week
- Month
- Year
Tableau has additional documentation on DATEDIFF and other date functions here: Date Functions – Tableau
Calculating Days Between Dates with DATEDIFF
To calculate the number of days between dates in Tableau, use the DATEDIFF function by creating a New Calculated Field. Then use the formula: DATEDIFF(‘day’, [Start Date], [End Date]) where ‘day’ defines the date_part and the start and end dates define the columns to apply the calculation to.
The following formula is used to calculate the number of days between two dates using DATEDIFF.
DATEDIFF('day', [Start Date], [End Date])Here is the same formula applied to a data set to create a new calculated column.
DATEDIFF can be adjusted to use different date_parts for different time intervals.
Calculating Months Between Dates with DATEDIFF
To calculate the number of days between dates in Tableau, use the DATEDIFF function by creating a New Calculated Field. Then use the formula: DATEDIFF(‘month’, [Start Date], [End Date]) where ‘month’ defines the date_part and the start and end dates define the columns to apply the calculation to.
The following formula is used to calculate the number of days between two dates using DATEDIFF.
DATEDIFF('month', [Start Date], [End Date])Calculating Days from Today
To calculate the number of days between a field value and today in Tableau, use the DATEDIFF function to specify the time interval as day, use a start date from a data set and define the end date with the today() function. The formula will look like DATEDIFF(‘day’,[Start Date],Today()).
Tableau will automatically return today’s date in the calculation whenever the report is scheduled to refresh. The following example shows the DATEDIFF function used to create a column to calculate the number of days between today and a Shipped Date.
Different date parts can be used with this method to calculate days, weeks, months, or years between a date and today’s date.