Date Functions in Tableau – Time to manipulate date values

Let us enhance our technical knowledge in Tableau and learn about some important date functions used in Tableau. In this article, we will learn about date functions in Tableau with their use in Tableau. Here, we will begin with understanding what are date functions, their types and how to create them.

What are Date Functions in Tableau?

As the name suggests, the date functions are used to work with the date records in a data source. Using the date functions in Tableau, we can manipulate date values by making changes in the old ones, searching specific date values or creating new ones.

Use of Date Functions in Tableau

With the help of date functions in Tableau, we can apply logical as well as arithmetic operations on date values as per our analysis requirements.

Date fields are very important in any data set and in the analysis as without date values like a year, month, week, quarter, etc, we will not have time references in our data.

Thus, with the help of date functions in Tableau, we can create date fields and perform complex operations on the date fields.

In the next section, we will see different types of date functions and learn their use.

Interpretation of Date Fields in Tableau

Whenever a new data set is uploaded, Tableau follows a set of procedures to identify a field as a date field.

1. The first step is to check the data values for any date values in it. Date values are generally distinguished from the other values by their special data type. Tableau identifies and verifies the date values or date records.

2. If the data field does not have proper data typing, we can manually change the data type of a date containing fields. Similar changes are also done to the fields containing a lot of null values.

3. In the above step, what we did was to convert or date values to a string type so that we can later use them to translate into date format through the Date Parse method.

The parsing method converts a simple string into a date format by assigning date-specific attributes to different parts of the date value.

4. After converting a simple value into date format, Tableau saves those values in a newly created date field using the date functions.

Types of Date Functions in Tableau

Let us learn about the different date functions, their expressions, and use in Tableau.

1. DATEADD

This function returns a date which is a result of adding a certain interval into the original date.

Syntax:

DATEADD(date_part, interval, date)

Example:

Suppose we want to add 6 months to the present date.

2. DATEDIFF

This function returns a difference between two dates.

Syntax:

DATEDIFF(date_part, date1, date2, [start_of_week])

Example:

To see the difference of weeks between two dates.

3. DATENAME

It returns the name of the part that you specify in the date_part parameter.

Syntax:

DATENAME(date_part, date, [start_of_week])

Example:

4. DATEPART

It returns the value entered in the date_part parameter as an integer value.

Syntax:

DATEPART(date_part, date, [start_of_week])

Example:

5. DATETRUNC

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

Syntax:

DATETRUNC(date_part, date, [start_of_week])

Example:

Truncating a date at a month level gives the first day of that month. Or truncating a date at a quarter level will give the first day of that quarter.

6. DAY

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

Syntax:

DAY(date)

Example:

7. ISDATE

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

Syntax:

ISDATE(string)

Example:

8. MAKEDATE

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

Syntax:

MAKEDATE(year, month, day)

Example:

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.

Syntax:

MAKEDATETIME(date, time)

Example:

10. MAKETIME

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

Syntax:

MAKETIME(hour, minute, second)

Example:

11. MAX

This function returns the bigger date when two dates are compared. Please note that the two dates must be of the same type.

Syntax:

MAX(expression) or MAX(expr1, expr2)

Example:

12. MIN

This function returns the smaller date of the two dates compared. This is the opposite of the MAX function.

Syntax:

MIN(expression) or MIN(expr1, expr2)

Example:

13. MONTH

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

Syntax:

MONTH(date)

Example:

14. NOW

This function returns the current date and time.

Syntax:

NOW( )

Example:

15. TODAY

This function returns the current date.

Syntax:

TODAY()

Example:

16. YEAR

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

Syntax:

YEAR (date)

Example:

How to Create and Use Date Functions in Tableau

Now that you know all the different types of date functions, learn from this section how to create date functions or expressions in Tableau. Follow the steps given below.

• To begin with, connect to a data source in Tableau.
• Go to an active worksheet.
• Add your fields in Rows and Columns as per your requirement for analysis.
• Now, select Analysis and then Create Calculated Field option.
• A calculation editor will open where you have to enter a name for your calculated field, for example, OrderDate.
• Also, here is where you enter the date function formula. For instance, DATETRUNC(‘quarter’, [OrderDate]).
• Click OK once you have entered your date expression or date function.
• The new calculated date field will appear with its name (given by you like we gave “OrderDate”) in the Dimensions section of Data pane.
• You use the new date field in your charts by dragging and dropping it on the Rows or Columns section.

Summary

This completes our discussion on the date functions in Tableau. Here, we learned what are date functions and why they are used in Tableau. Then, we went through a list of different types of date functions in Tableau. And finally, we saw how to create date functions or use date functions in date fields.

Next, its time to study about Tableau joins with DataFlair.

If you have any doubts in DataFlair’s date functions in Tableau article, mention in the comment section. Also, refer to our sidebar for more articles on Tableau.

Happy Learning😃