Date Functions in Tableau

Introduction:

In this article we will talk about the important date functions and how to manipulate date values when we are performing analysis in tableau. Often, we come across some scenarios where you have to just show the part of the date of add some extra days to date or extract part of date etc. So, let's see some of the important date functions in that are used in tableau.

With the help of date functions in tableau, we can apply logical as well as arithmetic operations on date values as per analysis. Date fields are very important in any data set. Manipulations can be done via calculated field in tableau and use them as per use case.

Types of Date Functions in Tableau:

• DATEADD

• DATEDIFF

• DATENAME

• DATEPART

• DATETRUNC

• DAY

• ISDATE

• MAKEDATE

• MAKETIME

• MAX

• MIN

• MONTH

• NOW

• TODAY

• YEAR

1. DATEADD

This function returns a date which is a result of adding some extra days/months to the original date

Syntax: DATEADD (date_part,interval,date)

Ex: adding 2 months to current date

2. DATEDIFF

This function returns a date which is a result of difference between the two dates in either month, date or days.

Ex: difference between order_date and ship_date

3. DATENAME

It returns the name of the part of the that we specify in the date_part parameter

Ex: extract month from date

4. DATEPART

It returns the value entered in the date part parameter as an integer value.

Syntax:

DATEPART(date_part, date, [start_of_week])

5. DATETRUNC

This function performs a truncate operation on the date values. It truncates the date to a specified accuracy or condition given in the expression and returns a new date.

Truncating a date at a month level gives the first day of that month, same can be done for quarter as well.

6. DAY

This function returns the day of the date as an integer.

7. ISDATE

It is a condition function that returns True if a given value or string is a date.

8. MAKEDATE and MAKETIME

Makedate function creates a date as per the specified year, month and day in the expression.

Syntax: MAKEDATE(YEAR,MONTH,DATE)

This gives the output as 1/4/2019

Maketime function creates a date value from given parameters like an hour, minute and second.

Syntax: MAKETIME(hour,minute,second)

9. MAKEDATETIME

This function creates a date and time value from the given input parameters. The date part in the input can be of date, string or datetime type but the time part must only be of datetime type.

10. MAX and MIN

MAX function returns the bigger date when two dates are compared, the two dates must be of the same type.

Min Function returns the smaller date when two dates are compared, this is the opposite of the Max function.

11.MONTH

This function returns the month of the given date in the form of an integer.

12.NOW and TODAY and YEAR

NOW function returns the current date and time.

Today returns the current date.

YEAR function returns the year of a given date in the form of an integer.

Syntax: NOW() , Today() and YEAR(date)

Example on creating Calculated field:

1. Click on the dropdown button and it shows the option for creating calculated fields

2. Edit the name and name it as desired name and add the syntax and click ok and then use it in analysis.

3. Use it in the either rows or columns

Summary:

This article completes the discussion of date functions in Tableau. We learned about how to use different defunctions as per use case in analysis by using them in calculated fields.