Date Functions in Tableau

# Date Functions in Tableau

clickHere
Vikram Singh
Assistant Manager - Content
Updated on Jun 14, 2022 10:25 IST

In Tableau, many functions are used in Tableau Calculation. This article will discuss Date Functions and how to create them in Tableau.

## Why use the Date Function?

Date functions allow the manipulation of dates in the data source.

Date fields are very important to get the time reference in the dataset. It analyses the record based on YEAR, MONTH, WEEK, QUARTER etc.

Let’s understand with the help of an example:

In the Sample Super Store Data, if you want to find the date difference between the Order Date and Ship Date, then you can use the formula:

Must Check: What is Tableau?

Must Check: Tableau Online Courses & Certifications

## Create a Date Calculation

We will learn how to create a date calculation with the help of an example using Sample Superstore Data.

Problem Statement-1: Find the average Date Difference between the Order Date and Ship Date.

Steps to Create

• Connect the Sample SuperStore Data
• Drag and Drop Region in Row Shelf
• Select Analysis -> Create Calculated Field
• Enter the name DATE DIFFERENCE
• Write the formula
• Click OK
• The new Calculated field (DATE DIFFERENCE) appears in the Data Pane under the Dimension
• Drag and drop DATE DIFFERENCE into Text in the Mark Pane
• Right-click on DATE DIFFERENCE -> Select Measure -> Average

From the above, the average delivery time in all the regions is approximately four days.

## Date Functions in Tableau

• It will add the specified interval to the specified date_part
• Example: Add 7 days to the date 09-09-2019

The above expression will return 16-09-2019 after adding seven days on 09-09-2019.

### DATEDIFF

• It will return the difference between the two given dates expressed in units of date_part
• Syntax: DATEDIFF(date_part, date1, date2, [start_of_week])
• Example: Find the Week difference between 04/06/2022 (Sunday) and 06-06-2022(Tuesday)

The above expression will return the value 1.

Note: If in the above expression week starts with ‘Sunday’, it will return the value 0 ( as both the dates belong to the same week)

### DATENAME

• It will return the date_part as a string.
• Syntax: DATENAME(date_part, date, [start_of_week])
• start_of_week parameter is an optional
• Example:

The above expression will return “2022.”

Note:  In the above expression, if we replace ‘year’ with ‘month’, it will return “June”.

### DATEPART

• It will return date_part as an integer.
• Syntax: DATEPART(date_part, date, [start_of_week])
• Example:

The above expression will return 2022.

### DATETRUNC

• This function returns a new date.
• It will truncate the specified date to the accuracy specified by the date_part
• Syntax: DATETRUNC(date_part, date, [start_of_week])
• Example:

The above expression will return 2022-06-01 12:00 AM

### DAY

• It will return the day as an integer
• Syntax: DAY(date)
• Example:

The above expression will return 06.

### ISDATE

• It is a kind of Boolean value that returns True or False
• True: if the given string is a date
• False: if the given string is not date
• Syntax: ISDATE(string)
• Example

The above expression will return true.

### MAKEDATE

• It will return a date value
• Syntax: MAKEDATE(year, month, day)
• Example:

The above expression will return #June 14, 2022#.

### MAKEDATETIME

• Returns a datetime that combines both date and time
• A date can be Datetime or string
• Time must be a Datetime
• Syntax: MAKEDATETIME(date, time)
• Example

The above expression will return #06/14/2022 02:35 PM#

### MAKETIME

• It will return a time
• Syntax: MAKETIME(hour, minute, second)
• Example:

The above expression will return #07:35:40#

### MAX

• It will return the maximum of a and b
• a and b must be of the same type
• Return NULL if anyone of the value is NULL
• Syntax: MAX(expression) or MAX( expression1 , expression2)
• Example

The above expression will return the last order date in the dataset.

### MIN

• It will return the minimum of a and b
• a and b must be of the same type
• Return NULL if anyone of the value is NULL
• Syntax: MIN(expression) or MIN( expression1 , expression2)
• Example

The above expression will return the first order date in the dataset.

### MONTH

• It will return the month as an integer.
• Syntax: MONTH(date)
• Example

The above expression will return 06.

### NOW

• It will return the current local system date and time.
• Syntax: NOW( )

### QUARTER

• It will return the quarter of the given date as an integer.
• Syntax: QUARTER ( )
• Example

The above expression will return Q1

### TODAY

• It will return the current date
• Syntax: TODAY()

### WEEK

• It will return the week of the given date as an integer
• Syntax: WEEK()
• Example

The above expression will return 3.

### YEAR

• It will return the year of the given date as an integer.
• Syntax: YEAR(date)
• Example

The above expression will return in 2022.

Note:

1. Along with the above date functions, Tableau has other functions like ISOQUARTER, ISOWEEK, ISOWEEKDAY, ISOYEAR. These functions return ISO8601 week-based quarter, week, weekday and year as an integer.
2. Tableau calculates the Date function using the traditional Georgian Calendar or ISO8601 Standards.
1. ISO8601 is different from the Georgian calendar; it is an international standard for calculating dates and times
2. In the Georgian calendar, we can define which day a week begins.
3. In ISO8601, the week always begins on Monday.
How to Create a Word Cloud in Tableau
In this article, we will briefly discuss about word cloud in tableau and how to create word cloud with an example.
How to Create a Forecast in Tableau
In this article, we will discuss what is forecasting, how does forecasting works in tableau, data requirement and constraints and finally how to create forecasting in tableau.
How to Create Hierarchies in Tableau
In this article, we will discuss hierarchies in tableau, how to create and remove hierarchies in tableau with examples.

## Conclusion

In this article, we have discussed different date function in tableau with the help of examples. We also discussed how to create Date Function in Tableau.