Date Functions

Version: 2020.1
Applies to: Tableau Desktop, Tableau Online, Tableau Public, Tableau Server

This article introduces date functions and their uses in Tableau. It also demonstrates how to create a date calculation using an example.

Create a date calculation

Follow along with the steps below to learn how to create a date calculation.

  1. In Tableau Desktop, connect to the Sample-Superstore saved data source, which comes with Tableau.

  2. Navigate to a worksheet.

  3. From the Data pane, under Dimensions, drag Order Date to the Rows shelf.

  4. On the Rows shelf, click the plus icon (+) on the YEAR(Order Date) field.

    QUARTER(Order Date) is added to the Rows shelf and the view updates.

  5. On the Rows shelf, click the plus icon (+) on the QUARTER(Order Date) field to drill down to MONTH(Order Date).

  6. Select Analysis > Create Calculated Field.

  7. In the calculation editor that opens, do the following:

    • Name the calculated field, Quarter Date.

    • Enter the following formula:

      DATETRUNC('quarter', [Order Date])

    • When finished, click OK.

      The new date calculated field appears under Dimensions in the Data pane. Just like your other fields, you can use it in one or more visualizations.

  8. From the Data pane, under Dimensions, drag Quarter Date to the Rows shelf and place it to the right of MONTH(Order Date).

    The visualization updates with year values. This is because Tableau rolls date data up to the highest level of detail.

  9. On the Rows shelf, right-click YEAR(Quarter Date) and select Exact Date.

  10. On the Rows shelf, right-click YEAR(Quarter Date) again and select Discrete.

    The visualization updates with the exact quarter date for each row in the table.

Why use date functions

Date functions allow you to manipulate dates in your data source.

For example, you might have a date field with year, month, and day for each value (2004-04-15). From these existing values, you can create new date values with a date function, such as the DATETRUNC function. For example, you can find the date of the beginning of the quarter for any existing date value.

The date calculation might look something like this:

DATETRUNC('quarter', [Order Date])

So, if the original date is '3/27/2011', using the above calculation would return '1/1/2011' to indicate that Q1 started on January 1. If the original date is '5/3/2011', then the calculation would return '4/1/2011' to indicate that Q2 started on April 1, four months into the year.

See the Create a date calculation section below for an example.

Gregorian Calendar vs. ISO 8601 Standard

If you are using a .hyper extract, date functions can be calculated using the traditional Gregorian calendar or the ISO 8601 Standard. For more information on creating a .hyper extract, see Extract Upgrade to .hyper Format

The ISO 8601 format is an international standard for calculating dates and times that differs from the Gregorian calendar due to how the starting week of a year (Week 1) is calculated. In the Gregorian Calendar, the user can define on which day a week begins. In the ISO 8601 Standard, the week always begins on a Monday.

In a Gregorian Calendar when a new year starts, Week 1 of the year is counted as starting on the 1st of January, regardless of where in the weekday the 1st of January occurs. If January 1st falls on a Saturday, then Week 1 will have one day in it and Week 2 will begin on the following Sunday.

In the ISO 8601 format, Week 1 of a new year begins on a Monday and has four or more days in January. For example, if January 1st falls on a Saturday, then Week 1 will not begin until the following Monday, January 3rd. Calculating dates this way makes sure that there are a consistent number of days in Week 1 of a new year.

For more information on working with ISO 8601 dates, see ISO-8601 Week-Based Calendar.

Date functions available in Tableau:

Function

Syntax

Description

DATEADD

DATEADD(date_part, interval, date)

Returns the specified date with the specified number interval added to the specified date_part of that date.

Supports ISO 8601 dates.

Example:

DATEADD('month', 3, #2004-04-15#) = 2004-07-15 12:00:00 AM

This expression adds three months to the date #2004-04-15#.

DATEDIFF

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

Returns the difference between date1 and date2 expressed in units of date_part.

The start_of_week parameter, which you can use to specify which day is to be considered the first day or the week, is optional. Possible values are 'monday', 'tuesday', etc. If it is omitted, the start of week is determined by the data source. See Date Properties for a Data Source.

Supports ISO 8601 dates.

Examples:

DATEDIFF('week', #2013-09-22#, #2013-09-24#, 'monday')= 1
DATEDIFF('week', #2013-09-22#, #2013-09-24#, 'sunday')= 0

The first expression returns 1 because when start_of_week is 'monday', then 22 September (a Sunday) and 24 September (a Tuesday) are in different weeks. The second expression returns 0 because when start_of_week is 'sunday' then 22 September (a Sunday) and 24 September (a Tuesday) are in the same week.

DATENAME

DATENAME(date_part, date, [start_of_week])

Returns date_part of date as a string. The start_of_week parameter, which you can use to specify which day is to be considered the first day or the week, is optional. Possible values are 'monday', 'tuesday', etc. If start_of_week is omitted, the start of week is determined by the data source. See Date Properties for a Data Source.

Supports ISO 8601 dates.

Examples:

DATENAME('year', #2004-04-15#) = "2004"
DATENAME('month', #2004-04-15#) = "April"

DATEPART

DATEPART(date_part, date, [start_of_week])

Returns date_part of date as an integer.

The start_of_week parameter, which you can use to specify which day is to be considered the first day or the week, is optional. Possible values are 'monday', 'tuesday', etc. If start_of_week is omitted, the start of week is determined by the data source. See Date Properties for a Data Source.

Note: When the date_part is weekday, the start_of_week parameter is ignored. This is because Tableau relies on a fixed weekday ordering to apply offsets.

Supports ISO 8601 dates.

Examples:

DATEPART('year', #2004-04-15#) = 2004
DATEPART('month', #2004-04-15#) = 4

DATETRUNC

DATETRUNC(date_part, date, [start_of_week])

Truncates the specified date to the accuracy specified by the date_part. This function returns a new date. For example, when you truncate a date that is in the middle of the month at the month level, this function returns the first day of the month. The start_of_week parameter, which you can use to specify which day is to be considered the first day or the week, is optional. Possible values are 'monday', 'tuesday', etc. If start_of_week is omitted, the start of week is determined by the data source. See Date Properties for a Data Source.

Supports ISO 8601 dates.

Examples:

DATETRUNC('quarter', #2004-08-15#) = 2004-07-01 12:00:00 AM
DATETRUNC('month', #2004-04-15#) = 2004-04-01 12:00:00 AM

DAY

DAY(date)

Returns the day of the given date as an integer.

Example:

DAY(#2004-04-12#) = 12

ISDATE

ISDATE(string)

Returns true if a given string is a valid date.

Example:

ISDATE("April 15, 2004") = true

MAKEDATE

MAKEDATE(year, month, day)

Returns a date value constructed from the specified year, month, and date.

Available for Tableau Data Extracts. Check for availability in other data sources.

Example:

MAKEDATE(2004, 4, 15) = #April 15, 2004#

MAKEDATETIME

MAKEDATETIME(date, time)

Returns a datetime that combines a date and a time. The date can be a date, datetime, or a string type. The time must be a datetime.

Note: This function is available only for MySQL-compatible connections (which for Tableau are MySQL and Amazon Aurora).

Examples:

MAKEDATETIME("1899-12-30", #07:59:00#) = #12/30/1899 7:59:00 AM#
MAKEDATETIME([Date], [Time]) = #1/1/2001 6:00:00 AM#

MAKETIME

MAKETIME(hour, minute, second)

Returns a date value constructed from the specified hour, minute, and second.

Available for Tableau Data Extracts. Check for availability in other data sources.

Example:

MAKETIME(14, 52, 40) = #14:52:40#

MAX

MAX(expression) or MAX(expr1, expr2)

Usually applied to numbers but also works on dates. Returns the maximum of a and b (a and b must be of the same type). Returns Null if either argument is Null.

Examples:

MAX(#2004-01-01# ,#2004-03-01#) = 2004-03-01 12:00:00 AM
MAX([ShipDate1], [ShipDate2])

MIN

MIN(expression) or MIN(expr1, expr2)

Usually applied to numbers but also works on dates. Returns the minimum of a and b (a and b must be of the same type). Returns Null if either argument is Null.

Examples:

MIN(#2004-01-01# ,#2004-03-01#) = 2004-01-01 12:00:00 AM
MIN([ShipDate1], [ShipDate2])

MONTH

MONTH(date)

Returns the month of the given date as an integer.

Example:

MONTH(#2004-04-15#) = 4

NOW

NOW( )

Returns the current date and time.

The return varies depending on the nature of the connection:

  • For a live, unpublished connection, NOW returns the data source server time.

  • For a live, published connection, NOW returns the data source server time.

  • For an unpublished extract, NOW returns the local system time.

  • For a published extract, NOW returns the local time of the Tableau Server Data Engine. When there are multiple worker machines indifferent time zones, this can produce inconsistent results.

Example:

NOW( ) = 2004-04-15 1:08:21 PM

QUARTER QUARTER ( )

Returns the quarter of the given date as an integer.

Example:

WEEK (#2004-04-15#) = 16

TODAY

TODAY( )

Returns the current date.

Example:

TODAY( ) = 2004-04-15

WEEK WEEK( )

Returns the week of the given date as an integer.

Example:

WEEK (#2004-04-15#) = 16

YEAR

YEAR (date)

Returns the year of the given date as an integer.

Example:

YEAR(#2004-04-15#) = 2004


date_part values

Many date functions in Tableau use date_part, which is a constant string argument.

The valid date_part values that you can use are:

date_part Values
'year' Four-digit year
'quarter' 1-4
'month' 1-12 or "January", "February", and so on
'dayofyear' Day of the year; Jan 1 is 1, Feb 1 is 32, and so on
'day' 1-31
'weekday' 1-7 or "Sunday", "Monday", and so on
'week' 1-52
'hour' 0-23
'minute' 0-59
'second' 0-60
'iso-year' Four-digit ISO 8601 year
'iso-quarter' 1-4
'iso-week' 1-52, start of week is always Monday
'iso-weekday' 1-7, start of week is always Monday

For more information on formatting date functions, see the Literal Expressions section of the At a glance: calculation syntax table.

Note: Date functions do not take account of the configured fiscal year start. See Fiscal Dates for more information.

Note: date_partreturns an integer. The values below are inputs that date_part accepts and the format of that input.

See Also

Fiscal Dates

Dates and Times

Date Properties for a Data Source

Custom Dates

Custom Date Formats

Tableau Functions (Alphabetical)

Tableau Functions (by Category)

Formatting Calculations in Tableau

Functions in Tableau

Back to top
Thanks for your feedback! There was an error submitting your feedback. Try again or send us a message.