KNOWLEDGE BASE

Difference in Dates with One Date Field


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

Finding the difference in dates with the DATEDIFF() function is simple if you have two date dimensions. However, in some cases you might have only one date field, and you want to calculate the difference in dates within that field. The example in this article, along with the attached workbook, OneFieldDateDiff1.twbx, show how to do this.

Create a calculated field for a reference date

The trick here is to compute the difference between each date and a constant reference date. Then use a quick table calculation on this computation to show the difference between dates in your date field.

Step 1

Select Analysis > Create Calculated Field.

Step 2

In the Calculated Field dialog box, do the following tasks:

  1. Name the field Reference Date.
  2. In the Formula box, build the following formula:

    DATE("1/1/1900")

Note: The reference date is arbitrary. As long as the dates in your data are later than this reference date, you should not expect any negative values.

Create a calculated field for the difference in dates

Step 1

Select Analysis > Create Calculated Field.

Step 2

In the Calculated Field dialog box, complete the following steps.

  1. Name the field Difference in Dates.
  2. In the Formula box, build the following formula:

    DATEDIFF("month", MAX([Reference Date]), MAX([Date]))

Note: In this example, you are calculating the difference in months between dates. If you wanted to calculate years or days, replace month with year or day in the above calculation. Also make sure to replace the [Date] field in this example with the name of the date field in your data (which is listed under Fields in the Calculated Field dialog box).

Create the view

Step 1

  • From the Dimensions pane, drag Date to the Columns shelf.
  • From the Measures pane, drag Difference in Dates to Text on the Marks card.

Step 2

Do the following to change the date format:

  1. In Tableau Desktop 8.0, on the Columns shelf, right-click Year(Date) and select More > Custom.

    In Tableau Desktop 7.0, on the Columns shelf, right-click Year(Date) and select More.

  2. In the Details drop-down list, select Month/Day/Year.

Step 3

On the Marks card, right-click AGG(Difference in Dates) and select Quick Table Calculation > Difference.

This returns the difference in months through each date along a single date field.

 

Alternate Search Terms:date, time, difference, difference in time
Did this article resolve the issue?