What is DATEDIFF function in Tableau?
The DATEDIFF in Tableau is used to calculate the difference between given two dates or datetime values. DATEDIFF function in Tableau returns the result in a specified time unit.
Below is the syntax of the DATEDIFF() function:
DATEDIFF(date_part, start_date, end_date)
Explanation of syntax and arguments
- date_part: Specifies the time unit in which you want to calculate the difference. This can be one of the following values: ‘day’, ‘hour’, ‘minute’, ‘second’, ‘week’, ‘month’, ‘quarter’, or ‘year’.
- start_date: The start date of the period you want to calculate the difference for.
- end_date: The end date of the period you want to calculate the difference for.
You can also use this DATEDIFF function inside any other function like IF condition, CASE function etc…
For example, if you want to calculate the number of days between two dates, you would use the following syntax:
DATEDIFF('day', start_date, end_date)Below are 4 examples of using the DATEDIFF() function:
Example #1: DATEDIFF in Tableau to Calculate number of days between two dates
Lets take example that we have two Order Date and Ship Date in the dataset and you would like to calculate number of days between these two days then below is the formula used using Datediff in tableau
DATEDIFF('day',[Order Date],[Ship Date])below is the output of Day calculation return
Example #2: Calculate number of months between two dates
Again lets work with two date that is Order Date and Ship Date in the dataset and you would like to calculate number of Months between these two days then below is the formula used using Datediff in tableau
DATEDIFF('month', [Order Date],[Ship Date])Below is the output of Day calculation return
Example #3: Calculate number of years between two dates
Now, calculate number of Months between these two days then below is the formula used using Datediff in tableau.
DATEDIFF('year', [Order Date],[Ship Date])Below is the output of Day calculation return
Example #4: Calculate the number of hours between two datetime values
DATEDIFF('hour', #2022-02-01 12:00:00#, #2022-02-02 14:30:00#)This returns the result of 26, since there are 26 hours between February 1st 12:00 PM and February 2nd 2:30 PM.
In each example, the DATEDIFF() function takes two date or datetime values, a time unit, and calculates the difference between the two values in the specified time unit. The resulting value can be used in calculations or displayed in a visualization.