Date Differences in Tableau

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.

Please remember that this function only works on date fields so if your date is still in string format (the little ABC icon on the data fields pane) you’ll need to first convert it to a date field data type.

1 reply
  1. Russ
    Russ says:

    I work with a school. The spreadsheet we have has several columns, including three date columns – Inquiry Date, Tour Date, Enroll Date. There is also an Admissions Director column (we have one admissions director for each of the four campuses). The spreadsheet has several hundred rows.

    There are a few things we’re trying to calculate. This tutorial helped, but I’m stuck on a few things. We need to figure out the average number of days, by month, and by admissions director that it takes for a lead to go from Inquiry Date to Tour Date. We need the same average for Tour Date to Enroll Date (again, by month and by admissions director). Ideally, I’d like to have a bar chart with a column for each month and then the each month has four bars that show the average number of days for each admissions director.

    I don’t know if that makes sense. But I’d appreciate your help. Thanks!

    Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.