The Data School - Congratulations, you've got yourself a date!

Tableau has a great assortment of DATE functions, all built to help manipulate date fields in the most useful way possible. With a bit of creativity and a good understanding of these functions, your ability to leverage Tableau will noticeably improve.

Here, I will run through the 'Big 6' DATE functions; these can be combined and interchanged to create an assortment of outcomes and views, all with distinct uses and cases. I will be using examples from the “Sample - Superstore” dataset.

1. DATEPART( [date_part, date , [start of week] )

DATEPART gives you back an integer value for the part of the date you have designated in the function. This is useful for giving numerical values to specific segments of a date, such as counting the weeks in a year. You can also optionally calibrate the starting day of the week.

Example: DATEPART(‘week’,[Order Date])

Figure 1: The Month and the corresponding number given by DATEPART

2. DATENAME( [date_part, date , [start of week] )

DATENAME is similar to DATEPART, but instead of giving an integer value, returns the string name defined by the date part.

Example: DATENAME(‘week’,[Order Date])

Figure 2: The same as Figure 1, but with the added DATENAME field in between

3. DATETRUNC( [date_part, date , [start of week] )

DATETRUNC is my favourite of these five functions, as it works by truncating the date to a specified level of date part. This allows you to control the field to a particular level of granularity, great if creating groups, colouring fields or just navigating between multiple date-based datasets. It will automatically format as a Date & Time field, so make sure to change this just to Date if that’s the desired output.

Example: DATETRUNC(‘month’,[Order Date])

Figure 3: DATETRUNC showing the truncation to only the **month** level

4. DATEADD( date_part, interval, date )

DATEADD works by subtracting or adding a specified number of your date part to the original value. This can be useful in calculating values for metrics like shipping time, or even just calculating the date a certain time from now. Again, this calculation will automatically format to a Date & Time field. In the example, you can see the jump ahead of 3 months, as specified in my calculation.

Example: DATEADD(‘month’,3,[Order Date])

Figure 4: DATEADD adding 3 months to the original Order Date, hence starting from April (04)

5. DATEDIFF( date_part, start_date, end_date, [start_of_week] )

This works well if you have defined start and end dates (as you may do with the previous function). Here, you can specify the level of date part to find the difference between two dates, as well as choosing the start of week date if you feel so inclined. You can hard-code in a date for your start/end point (enclosing the date with #), or use a field for both start and end.

Example: DATEDIFF(‘day’, #03/01/2018#, [Order Date])

Congratulations, you've got yourself a date! | 'DATE' Calculations