Calculating Date Differences in Tableau
One of my favorite functions in Tableau is the DATEDIFF function. When DATEDIFF is used within a calculated field, you can quickly start calculating date differences in tableau using two dates fields. The resulting calculated field will generate a brand new value on the fly. All you need to do is specify a start, and end date. Along with which piece or part of the date you wish to calculate by. The following table contains a comprehensive list of the functions date_part values:
| date_part | Values |
|---|---|
'year' |
Four-digit year |
'quarter' |
1-4 |
'month' |
1-12 or “January”, “February”, and so on |
'dayofyear' |
Day of the year; Jan 1 is 1, Feb 1 is 32, and so on |
'day' |
1-31 |
'weekday' |
1-7 or “Sunday”, “Monday”, and so on |
'week' |
1-52 |
'hour' |
0-23 |
'minute' |
0-59 |
'second' |
0-60 |
The syntax you’ll need to use is as follows:
DATEDIFF(‘day‘, [START_DATE], [END_DATE])
Let’s say in this example that we have a start date of July 1st 2016 and an end date of July 2nd 2016. We decide to figure out the number of days between the two fields by specifying that value in the first part of our DATEDIFF formula. Our calculation would yield 1 as the two dates are one day apart. If we were to swap out ‘day’ with ‘week’ our calculation would yield 0 as there is less than 1 week difference between the two dates.
Feel free to try out the DATEDIFF function on one of your own Tableau workbooks that contains multiple date fields to practice how the function works and begin calculating date differences in Tableau. Check out the video I’ve created below detailing how this function works when comparing the date a product was ordered to when it was actually shipped out to the customer. We can use this new calculated time to ship field to further analyze our various dimensions to see potential snags in our shipping process and get a better grasp on the product categories which need the most attention to get back on track.
