How to Use Date Functions in Tableau

In the present age, data visualization tools are becoming much more popular. One such Business Intelligence tool that can help you evaluate data consistently to gain customer insights in Tableau. When it is about calculating dates in tableau, your go-to solution would be the date functions. 

Tableau offers a variety of date functions, such as today, now, date name, dateadd, datepart, maketime, makedate, day, month, year, and much more. With a better and comprehensive understanding of date and functions, you would be able to build more efficient dashboards. In this article, let’s concentrate on tableau date functions and find out more about the same. 

Table of Content -Tableau Date Functions

What are Date Functions in Tableau? Why use Date Functions? Gregorian Calendar vs. ISO 8601 Standard What are the Date Parts in the Tableau? Date Functions in Tableau Tableau DATEADD Tableau DATEDIFF Tableau DATENAME Tableau DATEPARSE Tableau DATEPART Tableau DATETRUNC Tableau DAY Tableau ISDATE Tableau MAKEDATE Tableau MAKEDATETIME Tableau MAKETIME Tableau MAX Tableau MIN Tableau MONTH Tableau NOW Tableau QUARTER Tableau TODAY Tableau WEEK Tableau YEAR

What are Date Functions in Tableau?

Just as the name suggests, the date functions are mainly used to work with the records of date in a data source. By using the tableau date function, you can effortlessly manipulate the value of date by making certain changes and alterations in the older ones. 

Not just that, you can also search certain date values and create new ones as well. 

Why use Date Functions?

With the tableau date function, you can apply logical and arithmetic operations on the date value according to the requirements of your analysis. Date fields are essential in almost every data set,, as without the date values, you will not have time reference in your data. 

Therefore, with the help of tableau date functions,  you can effortlessly create date fields and perform complex operations. 

If you want to enrich your career and become a professional in Tableau, then enroll on "MindMajix's Tableau Training" - This course will help you to achieve excellence in this domain.

Gregorian Calendar vs. ISO 8601 Standard

In case you are making use of a .hyper extract, the date functions can be calculated with the help of a traditional Gregorian calendar or the ISO 8601 Standard. The ISO 8601 is the international standard meant to calculate times and date that is different from the Gregorian calendar as to how the beginning of a week of a year is calculated. 

In this type, a week always starts on a Monday. On the other hand, in the Gregorian calendar, you can define the day on which the week is beginning. Herein, whenever a new year begins, Week 1 of the year is calculated as the beginning, which is on the 1st of January, irrespective of whether it is a weekday or a weekend. 

What are the Date Parts in the Tableau?

In the tableau, a date part has the following values:

• Second (0 - 60)
• Year (Four digit representation)
• Minute (0 - 59)
• Quarter (1 - 4)
• Hour (0 - 23)
• Month (1 - 12 or by name, such as January, February, etc.)
• Day (1 - 31)
• DayofYear (1 - 365)
• Week (1 - 52)
• Weekday (1 - 7 or by name, such as Monday, Tuesday, etc.)

Date Functions in Tableau

Jotted down below are the primary tableau date functions that you can use with your dataset: 

Tableau DATEADD

With this specific function, you can easily add a certain time period to date. You can also specify the date part that you wish to add. And, with this function, you can add the date you selected. This one is efficient whenever you would have to calculate new dates, create reference lines, set time thresholds, or create new dimensions. 

Syntax: DATEADD(date_part, interval, date)

Tableau DATEDIFF

DATEDIFF helps calculate the difference of time between two dates at a level that you will be specified, such as hour, day, month, etc. By using [End Date] - [Start Date], will be giving you the precise time amount between two dates. 

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

Tableau DATENAME

The DATENAME function can be used to return the date part name. You can use the first argument to effortlessly specify a part of the date, and the function will accept the YEAR, MONTH, and DAY. 

Syntax: DATENAME(Date_part, Date)

Tableau DATEPARSE

With tableau DATEPARSE, you get to convert a string into a certain date format. Under this tableau date function, you will use:

• DateParse(format, string)
• DateParse(“dd.MMMM.yyyy”, “January 1, 2016”)

Below-mentioned are the acceptable formats for this function:

Date String	DATEPARSE Function	Display Value Returned
3.9.1994	DATEPARSE (“d.M/yy”, “3.9.94)	9/3/1994 12:00:00 AM
3/09/94	DATEPARSE (“d/M/yy”, 3/09/94”)	03/09/1994 12:00:00 AM
03Sep1994:9:8:7.6546	DATEPARSE (“ddMMMyyyy:H:m:s.SSSSSS”)	03/09/1994 9:08:09 AM
12:10	DATEPARSE(“h:m”, “12:10”)	12:10:00 PM

 

Read these latest Tableau Interview Questions and Answers that help you grab high-paying jobs

Tableau DATEPART

The DATEPART function is used to return or extract part of a specific date. You can use the first arg to specify the part of the date. This function is capable of accepting YEAR, MONTH, DAY, and more. For instance, if you have chosen Month as the date part, the function will then return a year from the given date. 

Syntax: DATEPART(Date_part, Date)

Tableau DATETRUNC

The DATERUNC function is used to return the first day of a certain date part. You can use the first arg to specify the date part and the function will accept YEAR, MONTH, DAY, and more. 

Syntax: DATETRUNC(date_part, Date)

Tableau DAY

The DAY function is to either return or extract the Day number from a specifically given date. 

Syntax: DAY(Date) 

Tableau ISDATE

In case you are looking forward to checking if the provided string is a date or not, this tableau date function will be used. It may return either true or false. 

Syntax: ISDATE(string)

Tableau MAKEDATE

The MAKEDATE function of tableau is used to return the date from a given year, month and day. 

Syntax: MAKEDATE(year, month, day)

Tableau MAKEDATETIME

With this tableau date function, you can return both date as well as the time from the given date and time data. 

Syntax: MAKEDATETIME(Date, Time)

Tableau MAKETIME

Another one that you can use is this MAKETIME function. This one is used to return time from an hour, minute as well as seconds. 

Syntax: MAKETIME(hour, minute, second).

Tableau MAX

This tableau date function is used to return the bigger date when two of them are compared. However, to make the comparison successfully, the two dates should be of a similar type.

Syntax: MAX(expression) or MAX(expr1, expr2)

Tableau MIN

With Tableau MIN, you can get the return of a smaller date when two of them are compared. Although this function is the opposite of max, the syntax remains somewhat similar.

Syntax: MIN(expression) or MIN(expr1, expr2)

Tableau MONTH

The month function is primarily used to return the Month number from a date that is available in the data. 

Syntax: MONTH(Date)

Tableau NOW

This is for returning the current time and date. This adds the hours, minutes as well as seconds. The NOW tableau date functions are useful in case you would need more granular information. It is also useful if you are working with intra-day data or such data that gets updated in almost real-time throughout the day. 

Syntax: NOW()

Tableau QUARTER

Another significant function on the list is this tableau QUARTER. Basically, this one is used to return the quarter of the given data. Whatever results you get in return are in the form of an integer. 

Syntax: QUARTER()

Tableau TODAY

TODAY is useful when it comes to creating reference lines that show the relation in the data to today (for example, your deadline that is in relation to today), along with the calculations that are based on the date of today. 

Syntax: TODAY()

Tableau WEEK

Just as the name suggests, this tableau date function helps you return the week of a specific given date. The returns or outcomes that you get are in the form of an integer. 

Syntax: WEEK()

Tableau YEAR

This function is mainly used to return the year from the date given in the data. You will get the return in the form of an integer. For instance, YEAR(#2021-03-16#) = 2021

Syntax: YEAR(Date)

Conclusion:

Now that you have a complete, comprehensive understanding of tableau date functions, you are ready to use them with your data. Now, get to know more about them and create date functions or use a variety of them in date fields without any worries.