The possibilities of Tableau as an analytical tool are limitless, especially when it comes to Tableau calculated fields. There is so much you can do with the help of calculated fields – you can manipulate your data, you can improvise with it, and get powerful insights from it. One of the most commonly used calculated fields are the Date Functions. And this is natural since it is really rare to come up with data that is static and not dependable on periods and dates. In most cases, the database comes up with distinct dates in it which enables us to open our analytical horizons and compare the data between periods. That is why, when it comes to Date Functions in Tableau, this article will present your go-to guide where you can find all the important information. First, we will try with a brief overview of what Date Functions really are, the most commonly used types of it in Tableau, and how to create them in Tableau. 

So, let’s enhance our technical knowledge in Tableau!

What are Date Functions in Tableau? 

So simply said, date functions are functions that enable us to work with date records in a certain data source that we are exposed to. By using these Date Functions, we are able to make certain changes in the old dates, search for some specific date or create new ones. That being said, Date Functions help us manipulate the date data that we have and adjust it to the needs of our analysis. Date functions are essential for the Tableau Data Analyst Certification Exam.

Read more: Create Dynamic Tableau Sets to Find Top N Contributors »

How Can We Use Date Functions in Tableau?

We can use the Date Functions in many different ways to manipulate with the data that we have. Overall, with the help of date functions in Tableau, we can perform logical as well as arithmetic operations on the date records in our database, as our analysis require. 

As we said above, almost every data set that we run into has at least one relevant date field in it that we use as a reference. Without it, it is a static database and cannot be further used for tracking important key performance indicator over time, or for comparing the same metrics over different periods. This just emphasizes the importance of date fields in Tableau. Date functions are also important for the Tableau Desktop Specialist Certification Exam. And as a result, the importance of Date Functions. Without them, we will have to use the date fields as given without the possibility for any further manipulation, for the cause of greater utilization. 

Thus, by using the date functions in Tableau we are able to create new date fields without permanent change of the old ones and perform simple or more complex operations on the newly created date fields.

How Does Tableau Interpret Date Fields?

Whenever a new data source is being uploaded to Tableau, Tableau follows its internal procedure to identify a certain field as a date field. 

First, Tableau checks the data values and search for any date values in them. Date values can be easily distinguished from the other fields in a database due to their specific data type. Even though they are many formats of how a date field may appear in a database, all of them can be easily recognized from Tableau. Once Tableau identifies and verifies that a certain data field has date values in it, it automatically assigns a certain symbol to the field to describe it as a Date or Date & Time field. 

Keep reading: Tableau Desktop Specialist Certification Questions »

If a certain data field does not have accurate data typing, there is a high possibility that it may not be properly identified by Tableau. In this case, we can manually change the data type of a field that contains date values in it. 

The easiest way to do that is to go to the Data Pane and click the data type icon. A new drop-out menu will appear where we can choose Date or Date & Time as a date type. In order to make sure that the change was successful, we need to drop the field to the view or the Data Source pane. If we see NULL values, means that the field can not be easily changed just by changing the data type. In this case, we need to use the DATEPARSE function, which we will explain later in this article. In simple words, the parsing method will help us convert a simple string into an accurate date format by assigning different date-attributes to different parts of the value in the field. It is also important for the Tableau Data Analyst Certification Exam. But we will dig into this later in the article. 

How to Create Date Functions in Tableau?

Date Functions in Tableau are created as any other calculations. That being said, you should to the Data Pane, right-click anywhere and the Calculated Field window will bring up. Then, select Create Calculated Field… 

Keep on reading: Tableau Data Analyst Certification Questions »

Once the Calculated Field window opens, you should expand it by clicking on the arrow on the right. Here, you can see all the functions that are available in Tableau. To filter out only the Date Functions, you should use the drop-down box and select Date. Now, you can see all the functions related to Date, an explanation of how to use them, and a simple example. 

Protip – Allowed values for date_part

It is also important to define the potential values that can be entered as for date_part, which is used in many Date Functions. The date_part can have one of the following values:

Date_part	Allowed values:
Second	0-60
Minute	0-59
Hour	0-23
Day	1-31
Weekday	1-7 or by name, e.g “Saturday”
Week	1-52
Day or Year	1-365
Month	1-12 or by name, e.g “July”
Quarter	1-4
Year	Four-digit, e.g “2021”

One thing worth mentioning regarding the allowable values for a date, in all Date Functions the date should be written between two hashtags ( “#” ), for example ‘#2021-01-25#’. This is a required syntax for Tableau to properly read the date that you want to specify.

Types of Date Functions in Tableau

Since we gained an understanding of what Date functions are and how Tableau identifies them, it is time to learn the different types, their syntax, and how they can be used. 

1. DATEADD

The DATEADD functions allow you to specify a certain interval that should be added or deducted from a valid date field. Yes, deduct as well. Even though the name of this function contains the word ADD, it can be used for deducting as well, if you state the interval with a negative number. DATEADD is important for the Tableau Desktop Specialist Certification Exam. One thing important here is that the number that you list as an interval will be applicable for the date_part that you state. That being said, if you write 15 as an interval and ‘day’ as a date_part, your date field will be increased every time for fifteen days.

Syntax: 

DATEADD(date_part, interval, date)

As an example, you want to add three days to a valid date field. In order to do that, you need to apply this formula in your Calculated Field window. 

=DATEADD(‘day ’ ,3, #2021-01-01#) = 2021-01-04

2. DATEEDIFF

The DATEDIFF function automatically returns the difference between two periods that you manually write in the Calculated Field. Please note, that the difference will be expressed in the unit that will be determined in the date_part of the function. This Date Function is commonly used in Tableau since we all know how time-consuming can be to manually calculate the difference between two periods, especially when it comes to days. DATEDIFF is also important for the Tableau Data Analyst Certification Exam. Also since people are extensively comparing metrics between different periods in order to spot some trend, this calculation is commonly used to calculate the period between. 

Syntax:

DATEDIFF (date_part, date1, date2, start_of_week)

Let’s say that after looking at some graphs, you got an insight that in the period between 9^th of September and 5^th of January you have generated a certain amount of revenue. In order to calculate the average revenue per day, you first need to see the number of days taken into considerations. In this case:

=DATEDIFF(‘day’, #2020-09-05#, #2021-01-05#) = 122 days

3. DATENAME

You can use the DATENAME function in Tableau to return the date_part of the valid date field as a string. Or in other words, the name of the date part that you specify. This function is used for manipulation with the data that is currently available to us for further needs of our analysis. The [start_of_week] is an optional parameter.

Syntax:

DATENAME(date_part, date, [start_of_week])

As an example, let’s say that you are handling data that has the date values in an ‘mm-dd-yyyy’ format. You want to create a chart for sales per month, with bars for each month separately. Previewing the months with names will be far more convenient than displaying the date with only numbers below each bar. In order to do this, we need to create a new field with this formula and drag it to the Rows shelf. 

= DATENAME(‘month’, [Order Date])

4. DATEPARSE

The function DATEPARSE works in the opposite way of the previously explained function DATENAME. While DATENAME converts part of the date field into a string, DATEPARSE converts a string into a date/time. This function is used for manipulation with data if the primary data is not being recognized as a date. The way how this formula works is that you should state the exact format of the date that you already have so Tableau can read it accurately. That means you should use letters and characters at the exact position as in the original date field and also, the exact number of characters. Regarding the letters replacing the date parts, “m” is for the month, “d” for the day, and “y” for the year. So if your date is written as “29.12.2020”, you should specify “dd.mm.yyyy” as a format in your formula. Please note that, if the formula is not properly written, Tableau will not be able to match the date/time format, and will it will return NULL. 

Syntax:

DATEPARSE(format, string)

If you upload a new database into Tableau that has fields with date values in it with an ‘odd’ format there is a high possibility that Tableau will not be able to clarify this field as a date. In that case, using DATEPARSE is essential. Let’s say that one of the members of the date field is “29.December.2020”. In order to convert this string to a date you should use the following formula:

=DATEPARSE(‘dd.MMMM.yyyy’), ’29.December.2020’) = December 29, 2020

5. DATEPART

As the name indicates, the function DATEPART returns only one part of a date field, the one that you specify in the formula. This function is used in the opposite situation than the ones previously explained – we have a date field in our database that it is extended up to seconds level and we need only the month for our analysis. Let’s say you are ‘lucky enough’ to have more data to work with than you actually need. This can be easily solved with this function by extracting only the part of the field that you need for analysis. Please note that when you set weekday a date_part, the start_of_date parameter is not required because Tableau uses a fixed order to apply offsets.

Syntax:

DATEPART(date_part, date, start_of_week)

We will create a situation that will help you understand the usage of this function better. You are working as a business analyst in a big company. You need to create a Tableau report for the management board, where you need to present the profit per year. But the underlying data that is available for you is tracking the profit on a daily basis. Since the managers are interested in seeing the trendline of the profit over the years, not on daily basis, you need to extract only the year from the date field. That is why you need to apply this formula: 

=DATEPART(‘year’, [Full Date]) = 2020

6. DATETRUNC

The DATETRUNC function aims to help you round the date field towards the date_part that you will specify in the formula. This function works in a similar direction as the one described above, DATEPART but with a different concept. They are both used when we are dealing with data in a more extended manner than we actually need for the sake of our analysis. The concept is different because this function, DATETRUNC, does not extract only one part of the date. It displays the date field in the same format as given in the beginning, but it truncates the date to the accuracy of the date_part that you state in the formula. 

Syntax:

DATETRUNC(date_part, date, start_of_week)

To better understand, we will use the same example from the previously explained function. You need to narrow down your data up to year level, but you also want to keep the format as before, so you can use the same field for further analysis. This is where you use the DATETRUNC function. You can easily round it up by using this formula:

=DATETRUNC(‘year’, #2020-29-12#) = 2020-01-01

The following three functions are pretty self-explanatory by themselves and follow the same concept. You can have the same output as of these functions if you put the date field into the Rows or Column shelf on the View itself and change it to preview a specific date part as a dimension. 

7. DAY

The function DAY is pretty straight-forward by itself – it returns the day from a specific date as an integer. In both ways, you will extract only the day part as a number. As the name suggests itself, this function can be used for extracting only the day as a date part. This function can be more of a help when you are dealing with data for a period shorter than a month. If you are managing a data source that has more months in it, all the data that is aligned with that n-th day of a month will be aggregated under the same number for all months. But if this is the goal of your analysis, then it will be pretty convenient for you. 

Syntax:

DAY(date)

If the goal of your analysis is to follow the trend of a certain metric on a daily basis for the last fourteen days, maybe including the whole date format will just redirect the focus from the visualization to the many numbers that appear on the X-axis. Keep it simple and show only the day. You can achieve this by stating:

=DAY(#2021-01-13#) = 13

8. MONTH

The MONTH function returns only the month of a given date in a form of an integer. 

Syntax:

MONTH(date)

Following the previous example, you can extract only the month from the date field like:

=MONTH(#2021-01-13#) = 1

9. YEAR

The function YEAR returns only the year of a given date in a form of an integer. If you place a certain date field on the Rows shelf in the Data view, this is the first default format that you will have displayed. This is because Year is considered as the most widespread form of a date, and furthermore it can be expanded to month, day, etc. 

Syntax:

YEAR(date)

Regarding the previous example, you can easily extract only the year like this:

=YEAR(#2021-01-13#) = 2021

10. ISDATE

The following date function is not used for manipulation or analysis of a date field, but just as a logical test. The ISDATE function can help you create an indicator that will validate the date field and return ‘TRUE’ if it is a valid date field, and ‘FALSE’ if is not a valid date field. 

Syntax:

ISDATE(string)

Even though this function is pretty simple, it can be used in multiple situations to confirm that Tableau can recognize a certain field as a date or even to check if it is possible for that date to exist. Let’s check two examples:

= ISDATE(“December 25, 2020”) = true

=ISDATE(“February 29, 2021”) = false

The following three Date Functions in Tableau are kind of specific since they are not used for any data manipulation or analysis, but for creating data that don’t exist in the data set. In other words, the data that we manually create using these formulas can refer to:

• Manipulate with a part of the already existing data we have
• Data that we create for the sake of our analysis, e.g. missing specific dates in an underlying database. We create the dates that we are missing, so we don’t have blanks in our tables or charts
• ‘Dummy data’ that does not have any value, but displays how the real data would be normally presented when we have it, so we can get a sense upfront

11. MAKEDATE

The function MAKEDATE can create a date field as per the day, month, and year that you specify in the expression. The default output of this function comes as “MM dd, YYYY”. 

Syntax:

MAKEDATE(year, month, day)

As we said above, we can use this function to create data that we are missing in our database so we don’t deal with blanks in our visualizations. For example, let’s say you have a data set containing revenue data for the last fifteen days. If for some reason, you are missing data on a certain date, at that point the line will be stopped in the line chart or the cell will be empty in a table. This may create a false indicator that we need to update the data, not that is 0$ on that day. That is why, we should create this date manually and present it to the chart, so we can see that for that day we have no revenue.

=MAKEDATE(2020,5,26) = May 26, 2020

12. MAKEDATETIME

Basically, this function functions the same as the above, except where we have the possibility to state a certain time as well. So, the MAKEDATETIME function creates a date & time field based on the given input parameters. For the date part, you can input a date, string, or a date & time type of value/field, but for the time part, it always has to be a date & time value/field so it can be properly recognized by Tableau. 

Syntax:

MAKEDATETIME(date,time)

Sometimes even if some data is registered on the same day, it could be updated at a different time of the day. So, for the same day, we can have more than one record since the timestamp is everywhere different. In this case, we can easily manipulate these dates and keep the date as it is but round the time stamp at a one-time stamp for all. 

= MAKEDATETIME([Full Date & Time], #12:00:00#) 

13. MAKETIME

With the help of this function, MAKETIME, you can create a time value from the parameters that you input in your expressions. The parameters are an hour, minute, and second. This is actually the second part of the MAKEDATETIME function. 

Syntax:

MAKETIME(hour, minute, second)

Let’s say that you are creating a basic schema for a report that you have upcoming. You want to take care of the design of the report before you even have the data, just to make sure you will have everything on time. You want to have a specific box that will indicate when was the last time your data was updated. Since you don’t have this data already, you create a ‘dummy’ time field which can help you set things right in the report. You can create it like this:

=MAKETIME(11:30:00) = 11:30:00

14. MAX

As the name indicates itself, the MAX function returns the maximum value of a single expression. Or, you can compare two different date values and the MAX function will return the one that is considered as a bigger date. Please note that when comparing two different dates, they must be in the same format. MAX function as a function exist in several categories in functions, which means it can be used in other calculations as well, not only date-related calculations. If some of the arguments are NULL, the output of the function will also be NULL.

Syntax:

MAX(expression) or MAX(expression1, expression2)

We will go through two examples so we can cover up both situations. If you have underlying data that is being refreshed every day, you can use the MAX function to check when the last time your data was updated. The function works in a way that it checks the maximum date value from the date field that you specify. You can easily check that by applying this formula:

MAX([Insert Date]) = 2021-01-27

Or let’s say you are doing some more complex calculation where you need to check two dates, filter the bigger one and align it with the proper quantity sold on that day. In that case, you can use the MAX function to compare those two days and return the more recent one. 

MAX(#2020-01-27#, #2020-01-25#) = 2020-01-27

15. MIN

Like the MAX function above, MIN functions also appear in multiple categories of functions and it is used mostly as a Number Function, but it can also be used with dates. So, basically, the MIN function does the opposite of the MAX function – it returns the minimum value of a single expression or the minimum value of two expressions when being compared. If some of the arguments are NULL, the output of the function will also be NULL.

Syntax:

MIN(expression) or MIN(expreesion1, expression2)

We will look at the examples from above, from a different perspective now. If you have underlying data that is being updated on a daily basis, you can use the MIN function to check from which date you started receiving data. In other words, you can see what is the least recent date in your date. 

MIN([Insert Date]) = 2020-01-01

Or if we go through the same example from above, applying the MIN function to those two dates will return the following output:

MIN(#2020-01-27#, #2020-01-25#) = 2020-01-25

The following two functions in Tableau serve as an indicator, returning the current date or the current date & time. Please note that since ‘current date & time’ is a relative category due to different time zones, make sure to check the time zone of your Tableau and align the calculation properly. You can use this calculation for multiple purposes in your reports. These calculations do not have a defined syntax since they can have only one possible value/outcome. You just need to add additional brackets “( )” to the function in order for it to work. 

16. NOW

The NOW function returns the current date and time. 

= NOW () = 2021-01-27 12:00:00

17. TODAY

The TODAY function returns only the current date.

= TODAY () = 2021-01-27

Just a simple example of how you can use these two functions. Let’s say you are a gaming data analyst and you analyze the overall revenue that is being generated from one user since their first day of install. So as a result, you have dependable metrics such as Day 90 Revenue. Previewing this metric when nineteen days still haven’t passed will be inconvenient for the user of your report. That is why you can use the TODAY or NOW function, to indicate when nineteen days will pass and the metric will be relevant for displaying.

Summary 

This completes our discussion regarding the Date Functions in Tableau. Today, we learned that Date functions are functions in Tableau that enables you to work with date records in a certain data source. With the help of the Date Functions, you can manipulate the already existing data fields, create new ones and make changes for further analysis. Date functions are important for all Tableau Certification Exams. We also did a step-by-step tutorial on how to create Date Functions in Tableau and what values you can enter into so the function can be valid. And finally, we went through a list of different date functions that can be found in Tableau and a relevant example for each one of them for better understanding. Hope this article has helped you gained the desired knowledge and trained you to a level to create great Date Functions!

---

Get our Most Popular Downloads

Download the most popular scenario-based Tableau Workbooks in .twbx format. Used by thousands of Tableau developers and job aspirants every day to improve and fine-tune their CV and Tableau Public profile. Join the largest Tableau Experts Social Group.