Get Ready For Your Tableau Certification
  • Bernard

Tableau date functions


tableau date functions

Date functions are calculations used to manipulate date fields in our Tableau data source. For example, you may have a data source containing the Order date (date when an order is placed) and the Shipping date (date when an order is shipped to the customer). To compute, the time it takes to ship orders – you can use the DATEDIFF function, which allows you to compute the difference between two dates in the units specified on the date_part.

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

Therefore, in this article – I’ll be sharing some of the date functions you can use to manipulate date field in Tableau.

Date functions available in Tableau

1. DATEADD Function

This function returns the specified date, with the number specified on the interval added to the specified date part of that date.

Syntax:

DATEADD (date_part, interval, date)

Example:

To add four months to the current date, I can use this function as shown below.

DATEADD (‘month’, 4, #16/10/2021#) = #16/02/2022#

2. DATEDIFF Function

This function returns the difference between two dates, expressed in the units specified on the date part.

Syntax:

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

Example:

To compute the difference in weeks between the two dates.

DATEDIFF (‘week’, #10/10/2021#, #12/10/2021#, ‘monday’) = 1 (the two dates are on different weeks)

DATEDIFF (‘week’, #10/10/2021#, #12/10/2021#, ‘sunday’) = 0 (the two dates are on the same week)

Note: The [start_of_week] part is optional, and therefore when not specified – its determined by the data source.

Read about DATEDIFF function in detail

3. DATENAME Function

This function returns the date_part of a date field as a string.

Syntax:

DATENAME (date_part, date, [start_of_week])

Example: DATENAME (‘month’, #27/12/2020#) =’December’

DATENAME (‘year’, #27/12/2019#) = ‘2019’

4. DATEPARSE Function

This function returns a date_string as a date.

Syntax:

DATEPARSE (date_format, date_string)

Example:

DATEPARSE (‘yyyy/MM/dd’, #2021-10-16#) = ‘October 16, 2021’

5. DATEPART Function

This function returns the date_part of a date as an integer.

Syntax:

DATEPART (‘date_part’, date, [Start_of_week])

Example:

DATEPART (‘month’, #15/05/2021#) = 5

DATEPART (‘year’, #27/01/2021#) = 2021

6. DATETRUNC Function

This function truncates the specified date to the accuracy specified in the date_part.

Syntax:

DATETRUNC (‘date_part’, date, [start_of_week])

Example:

DATETRUNC (‘month’, #27/09/2021#) = #01/09/2021# (truncates the date from 27th to the start of the month).

DATETRUNC (‘quarter’, #17/03/2021#) = ‘01/01/2021’ (truncates the date from March 17th (which falls in quarter 1) to the date when the first quarter began).

7. DAY/WEEK/MONTH/QUARTER/YEAR Functions

These functions return the date part of the given date as an integer.

Syntax:

DAY (date)

WEEK (date)

MONTH (date)

QUARTER (date)

YEAR (date)

Example:

DAY (#16/10/2021#) = 16

WEEK (#16/10/2021#) = 42

MONTH (#16/10/2021#) = 10

QUARTER (#16/10/2021#) = 4

YEAR (#16/10/2021#) = 2021

8. MAKEDATE Function

This function returns a date value constructed from the specified year, month, and day.

Syntax:

MAKEDATE (year, month, day)

Example:

MAKEDATE (2021, 10, 17) = #17/10/2021# (October 17th, 2021)

9. MAKEDATETIME Function

This function returns a datetime that combines a date and a time.

Syntax:

MAKEDATETIME (date, time)

Example:

MAKEDATETIME (‘29/09/2021’, #08:35:00#) = #29/09/2021 08:35:00 AM#

10. MAKETIME Function

This function returns a date value constructed from the specified hour, minute, and second.

Syntax:

MAKETIME (hour, minute, seconds)

Example:

MAKETIME (18, 05, 58) = #18:05:58#

11. MAX/MIN Functions

These functions return the maximum or minimum date of the two dates in comparison.

Syntax:

MAX (date1, date2)

MIN (date1, date2)

Example:

MAX (#21/02/2021#, #03/10/2021#) = #03/10/2021#

MIN (#21/02/2021#, #19/11/2018#) = #19/11/2018#

12. NOW Function

This function returns the current local system date and time.

Syntax:

NOW ()

Example:

NOW () = #16/10/2021 18:15:21# (date and time of writing this article)

13. TODAY Function

This function returns the current date.

Syntax:

TODAY ()

Example:

TODAY () = #16/10/2021# (date of writing this article)

More date functions can be accessed on the Tableau calculated field editor – by selecting Date on the side menu.

tableau date functions on the calculated field editor

I hope this article was helpful to you. To receive more of the Tableau tips, kindly join our mailing list by subscribing below.

Thank you for reading.

LEAD MAGNET - 2.png
FREE INTRO COURSE -2.png