Date Functions in Tableau

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

Table of Content

• Why use the Date Function?
• Create a Date Calculation
• Date Functions 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.

Also Check: String Functions in Tableau: Find, Trim, Contains, Min

Also Check: Number Functions in Tableau: Mathematical, Trigonometric

Date Functions in Tableau

DATEADD

• It will add the specified interval to the specified date_part
• Syntax: DATEADD(date_part, interval, date)
• 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.

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.

Hope this article, will help you in your data analytics journey.

Top Trending Articles:

Introduction To Tableau | Products Of Tableau | Data Types In Tableau | Change Data Type In Tableau | File Type In Tableau | Data Aggregation | Connecting Data In Tableau | Dimension And Measure | Tableau Show Me | Joins In Tableau | Union In Tableau | Operators In Tableau | Number Functions In Tableau | String Functions In Tableau | Filter In Tableau | Context Filter In Tableau | Filters In Tableau | Types Of Charts In Tableau | Bar And Line Chart In Tableau | Pareto Chart In Tableau | Gantt Chart In Tableau | Box And Whisker Chart In Tableau | Waterfall Chart In Tableau | Donut Chart In Tableau | Funnel Chart In Tableau | Dual Axis Chart In Tableau | Sort Data In Tableau | Tableau Sets | Groups In Tableau | Tableau Hierarchy | Tableau Forecasting | Highlighting In Tableau | Word Cloud In Tableau | Cohort Analysis In Tableau