KNOWLEDGE BASE

## Calculate Difference in Dates with One Date Field

Published: 22 Mar 2017

### Question

How to calculate the difference between dates with only one date field.

### Environment

Tableau Desktop

Calculate the difference between each date and a constant reference date, then use a table calculation to show the difference between dates in your date field.
This example uses Superstore - Sample data.
Note: You can choose any date you like for a reference date, as long as all the dates in your data are later than the reference date.

### Step 1: Create a calculated field for a reference date

1. Select Analysis > Create Calculated Field
2. Name the field Reference Date, enter the following formula, then click OK
`DATE("1/1/1900") `

### Step 2: Create a calculated field for difference in dates

1. Select Analysis > Create Calculated Field
2. Name the field Difference in Dates, enter the following formula, then click OK
`DATEDIFF("day", MAX([Reference Date]), MAX([Order Date])) `
Note: You can choose any date part you like in place of "day". For more information, see Date Functions.

### Step 3: Create the View

1. Place [Order Date] on Rows
2. Place Difference in Dates on Text
3. Right-click Year(Date) and select More > Custom.
4. Select Month / Day / Year, then click OK
5. Right-click Difference in Dates on the Marks card and select Quick Table Calculation > Difference
You will now see the difference in days between each date on the view.

### (Optional) Step 4: Filter the view

Especially in data sources, like Superstore, that have few gaps in the data, filtering this view can make it more interesting and really show the technique working.
1. Drag Order Date to Filters.
2. Choose Month / Day / Year.
3. Choose Select from List
4. Filter the data to only a few dates and see the numbers change.