Show Table of Contents
This article introduces date functions and their uses in Tableau. It also demonstrates how to create a date calculation using an example.
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 orginal 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.
DATEADD - Returns the specified date with the specified number interval added to the specified date_part of that date.
DATEDIFF - Returns the difference between date1 and date2 expressed in units of date_part.
DATENAME - Returns date_part of date as a string.
DATEPART - Returns date_part of date as an integer.
DATETRUNC - Truncates the specified date to the accuracy specified by the date_part.
DAY - Returns the day of the given date as an integer.
ISDATE - Returns true if a given string is a valid date.
MAKEDATE - Returns a date value constructed from the specified year, month, and date.
MAKEDATETIME - Returns a datetime that combines a date and a time.
MAKETIME - Returns a date value constructed from the specified hour, minute, and second.
MAX - Returns the maximum date of dates a and b.
MIN - Returns the minimum date of dates a and b.
MONTH - Returns the month of the given date as an integer.
NOW - Returns the current date and time.
TODAY - Returns the current date.
YEAR - Returns the year of the given date as an integer.
For more detailed definitions of these functions, as well as syntax and examples, see Tableau Functions (by Category) and click Date functions.
Follow along with the steps below to learn how to create a date calculation.
In Tableau Desktop, connect to the Sample-Superstore saved data source, which comes with Tableau.
Navigate to a worksheet.
From the Data pane, under Dimensions, drag Order Date to the Rows shelf.
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.
On the Rows shelf, click the plus icon (+) on the QUARTER(Order Date) field to drill down to MONTH(Order Date).
Select Analysis > Create Calculated Field.
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.
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.
On the Rows shelf, right-click YEAR(Quarter Date) and select Exact Date.
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.
Many date functions in Tableau use
which is a constant string argument. The valid
that you can use are:
||1-12 or "January", "February", and so on|
||Day of the year; Jan 1 is 1, Feb 1 is 32, and so on|
||1-7 or "Sunday", "Monday", and so on|
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.
Dates and Times
Date Properties for a Data Source
Custom Date Formats
Tableau Functions (Alphabetical)
Tableau Functions (by Category)
Formatting Calculations in Tableau
Functions in Tableau