Tableau provides various Date Functions such as Year, Month, Day, makedate, maketime, datediff, datepart, dateadd, datename, datetrunc, now, today, etc. In this article, we will show you how to use Tableau Date Functions with examples.
To demonstrate these Tableau Date functions, we will use the data shown below. As you can see, there are 15 records in this table.
Tableau Date Functions
The following set of examples will show you the list of Tableau Date Functions
Year
The Tableau YEAR function is used to return Year from the given date and the syntax of this YEAR:
YEAR(Date)
To demonstrate the date functions in Tableau, we have to use Calculated Fields. To create a calculated field, navigate to the Analysis Tab and select the Create Calculated Field… option.
Once you click on the Create Calculated Field, the following window will open. Here, we renamed the default calculation name as Year.
As you can see from the screenshot below, while I was typing, the calculation window showed the suggestions. For more functions >> Click Here.
For the Tableau Date Functions demo purpose, we will return the Years from HireDate.
YEAR([HireDate])
Let me add this calculated field to the table (by dragging a field to Rows Shelf) that we created earlier. Please refer to the Create Table Report and Formatting articles to understand the steps involved in creating a table.
Because it is a measure, sometimes it may not show properly. If this is the case, click on the Year and change it from Measure to Dimension. Next, change the values from Continuous to Discrete.
Tableau MONTH function
The Tableau Month function is used to return the Month number from a given date, and the syntax of this Month is
MONTH(Date)
Let me create a calculated field to return the Month number from Hiredate
MONTH([HireDate])
Let me add this Month Number field to the Rows shelf
DAY
The Tableau DAY function is used to extract or return the Day number from a given date, and the syntax of this DAY is:
DAY(Date)
Let me create a Day field to return the Day number from HireDate
DAY([HireDate])
Next, we added the day field to the Row Shelf
Tableau Date NOW Function
The Tableau NOW function is used to return today’s date and time, and the syntax of this NOW is:
NOW()
Let me create one calculated field for the NOW function to return today’s date and time
NOW()
From the below screenshot, you can see today’s date and time
Tableau Today Date Function
The Tableau Today function is used to return today’s date, and the syntax of this Today is:
TODAY()
Let’s create a field to return today’s date
From the following screenshot, you can see today’s date
MakeDate Function
The Tableau MakeDate function is used to return date from the year, month, and day and the syntax of this MakeDate is:
MAKEDATE(year, month, day)
Let’s create another calculated field to return the date from the specified year, month, and day. As you can see, we used Year(), MONTH(), and DAY() functions to extract the corresponding values.
MAKEDATE(YEAR([HireDate]), MONTH([HireDate]), DAY([HireDate]))
You can see the MakeDate result
MakeTime Function
The Tableau MakeTime date function is used to return time from an hour, minute, and second, and the syntax of this MakeTime is:
MAKETIME(hour, minute, second)
Let me create a calculated field to return time from hours, minutes, and seconds. As you can see, we are giving static values. However, you can use Datepart to extract hours, minutes, and seconds from date and time.
MAKETIME(14, 22, 59)
Now, you can see the MakeTime Function result. Remember, 30 Dec, 99 is the default date generated.
Tableau MakeDateTime Function
The Tableau MakeDateTime function is used to return date and time from Date, Time, and the syntax of this MakeDateTime is:
MAKEDATETIME(Date, Time)
As you can see, we are using the previously created calculated fields as arguments. MakeDt will return Date from HireDate, and MakeTime will return a static time value.
MAKEDATETIME([MakeDt], [MakeTime])
Let me add this MakeDateTime field to the table.
ISDATE Function
The Tableau IsDate function is used to check whether the given string is a date or not. It returns either True or False. The syntax of this IsDate is:
ISDATE(string)
Let me use this one on the string Hire Date column. As you can see, this is a string column
ISDATE([StringHireDate])
You can see the result of this IsDate function
Tableau DATEDIFF Function
The Tableau DATEDIFF function returns the date difference between the start and the end date. Use the first argument to specify the difference term.
This datediff function accepts YEAR, MONTH, DAY, etc. For example, if you select YEAR, the DateDiff will return the number of years between a start date and the end date.
The syntax of this DATEDIFF is:
DATEDIFF(Difference_term, Start_Date, End_Date)
Let me create a calculated filed to check the difference between HireDate and Today in years using datediff.
DATEDIFF('year', [HireDate], NOW())
From the screenshot below, you can see the total number of years between HireDate and Today
Let me change the difference term to MONTH in datediff, and click the Apply button. Now you can see the difference between those dates in Months
Tableau DATEADD Function
The Tableau DATEADD function is used to add user-specified intervals to an actual date. Use the first argument to define the date part term and the second arg to specify the interval.
This DATEADD function accepts YEAR, MONTH, DAY, etc. For example, if you select Month as the first argument and interval as 6, then the Dateadd will add six months to the existing date.
The syntax of the DATEADD is:
DATEADD(Date_part, interval, Date)
The below statement will add 5 years to HireDate.
DATEADD('year', 5, [HireDate])
This time, we will use the month as the date part in the DATEADD. The statement below adds 6 months to HireDate.
DATEADD('month', 6, [HireDate])
Tableau DATEPART Function
The Tableau DATEPART function is used to extract or return part of a date. Use the first arg to specify the date part.
This DatePart function accepts YEAR, MONTH, DAY, etc. For example, if you select Month as the date part, then the DatePart function will return a year from a given date.
The syntax of this DATEPART is:
DATEPART(Date_part, Date)
The following statement will extract the day number from HireDate.
DATEPART('day', [HireDate])
This time, we will use Hour as the datepart. The Below statement will return Hour from HireDate
DATEPART('hour', [HireDate])
Tableau DATENAME Function
The Tableau DATENAME function is used to return the date part name. Use the first argument to specify the date part, which accepts YEAR, MONTH, and DAY.
The syntax of the DATENAME is:
DATENAME(Date_part, Date)
The statement below will return the month name from HireDate.
DATENAME('month', [HireDate])
This time we will use weekday as the date part in DateName. The below statement will return the weekday name from HireDate
DATENAME('weekday', [HireDate])
Tableau DATETRUNC Function
The Tableau DATETRUNC is one of the data functions. It is used to return the first day of the specified date part. Use the first argument to specify the date part, and this datetrunc accepts YEAR, MONTH, DAY, etc.
The syntax of the DATETRUNC is:
DATETRUNC(Date_part, Date)
The statement below will return the month’s starting date from HireDate.
DATETRUNC('month', [HireDate])
This time we will use a quarter as the date part in DATETRUNC. The below statement will return the first day of the quarter that the date belongs to
DATETRUNC('quarter', [HireDate])
